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
September 2, 2012
·
Infoyen ·
2 Comments
Tags: asp, MOSS, SharePoint, SharePoint 2010 · Posted in: ASP .NET, SharePoint, SharePoint 2010
2 Responses
Thanks for sharing this information!!
Thank you for sharing this information!
Leave a Reply