Table Variables ms sql

Issue Detail:

When I drag and drop store procedure into DBML file (Linq to Sql dbml file) it throws below error:-
“Unknown Return Type – The return types for the following stored procedures could not be detected”
or
“No return type for Store Procedure”

This happens due to various syntax issues which DBML does not like :-)
For example:-
– Create temp table does not work in temp table, we should use Declare Table.
– Calculated fields some time does not work, they require cast

Solution Detail:

Case 1 (CAST issue)

For example i have a variable (@CalculatedString) which has computed value. Now i want to append this variable into another variable and return.

So it would be like that:-
Set @FinalMessage = “Here is the result quote:-” + @CalculatedString

But this throws error in DBML so i convert into below line which works great:-
Set @FinalMessage = “Here is the result quote:-” + CAST(@CalculatedString as varchar)

Case 2 (Temp Table issue)

Rather than temp table use Table Variables. Below is the example:-

1
2
3
4

CREATE TABLE TempWork(
WorkId INT,
WorkDetails VARCHAR(50)
)

If you use above temptable and drag & drop into dbml file then it will throw below error:-
“Unknown Return Type – The return types for the following stored procedures could not be detected”

Now convert this temp table syntex into below code:-

1
2
3
4
5

DECLARE @TempWork TABLE
(
WorkId INT,
WorkDetails VARCHAR(50)
)

If you use above table and drag & drop into dbml file then it will work greats.
This is same as temptable, that means you can perform select, insert, update etc statements.

1
2
3
4
5

--For example insert statement can be like this:-
INSERT INTO @TempWork (WorkId, WorkDetails) VALUES (1,"Create an page which display news");
 
--Update statement can be like this:-
UPDATE @TempWork SET WorkDetails = "Create an page which display flash" WHERE WorkId = 1

Hope it helps you.
Regards,
Avinash

calendarSeptember 2, 2012 · cardInfoyen · comments2 Comments
tagTags: , , ,  · Posted in: ASP .NET, SharePoint, SharePoint 2010

2 Responses

  1. Doug Spence - September 30, 2012

    Thanks for sharing this information!!

  2. David - November 3, 2012

    Thank you for sharing this information!

Leave a Reply

Spam Protection: , required

myworldmaps infoyen