declare output of command - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

declare output of command

Author

Topic

magmo
Aged Yak Warrior

558 Posts

Posted - 2012-08-29 : 13:28:05

Hi

I have a stored procedure that generate a xml structured string as output, in the sp I concenate a sql command like this..


SET @sqlCommand = 'SELECT ' + @colList + ' FROM ' + @TableName + ' as Book'
SET @sqlCommand =  @sqlCommand + ' WHERE ' + @IdCol +' IN (SELECT Id FROM #Temp)'
IF(LEN(@Status) > 0) BEGIN
	SET @sqlCommand =  @sqlCommand + ' AND TypeOfB IN (SELECT value FROM  dbo.SplitStr(''' + @TypeB +''', ''|''))'
END
SET @sqlCommand = @sqlCommand + ' FOR XML AUTO,  ROOT (''root''), ELEMENTS'

EXEC (@sqlCommand)

it works, but my problem is that Visual Studio does not recognize the result type from the stored procedure. So I thought I added a ..


@Result xml  OUTPUT

But I don't how I would set the @Result value to the EXEC @sqlCommand and if it's possible. Anyone know how I can do that?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-29 : 13:38:33

You should not need to do that - it should be XML. You might try adding the "TYPE" option in your SQL string as shown in red below:


....
SET @sqlCommand = @sqlCommand + ' FOR XML AUTO,  ROOT (''root''), ELEMENTS, TYPE'
...

If you do want to set the result of the query to a variable you can use sp_executesql instead of EXEC to run the dynamic SQL and specify parameters. There is a description and examples here: http://support.microsoft.com/kb/262499

magmo
Aged Yak Warrior

558 Posts

Posted - 2012-08-29 : 14:02:09

I added the TYPE option but that didn'nt make any difference, I tried the approach in the link like this...

--EXEC (@sqlCommand)

DECLARE @SQLString NVARCHAR(4000)
DECLARE @ParmDefinition NVARCHAR(500)
DECLARE @MyOutPut xml

SET @SQLString = @sqlCommand
SET @ParmDefinition = '@ResultOUT xml OUTPUT'

EXECUTE sp_executesql
@SQLString,
@ParmDefinition,
@ResultOUT=@MyOutPut OUTPUT
SELECT @MyOutPut

But that didn't help either, I stil get the same error, but the procedure itself work.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-29 : 14:36:23

quote:
Originally posted by magmo

I added the TYPE option but that didn'nt make any difference, I tried the approach in the link like this...

--EXEC (@sqlCommand)

DECLARE @SQLString NVARCHAR(4000)
DECLARE @ParmDefinition NVARCHAR(500)
DECLARE @MyOutPut xml

SET @SQLString = @sqlCommand
SET @ParmDefinition = '@ResultOUT xml OUTPUT'

EXECUTE sp_executesql
@SQLString,
@ParmDefinition,
@ResultOUT=@MyOutPut OUTPUT
SELECT @MyOutPut

But that didn't help either, I stil get the same error, but the procedure itself work.

whats the error you got?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

magmo
Aged Yak Warrior

558 Posts

Posted - 2012-08-29 : 15:07:33

This error in Visual Studio "The return type for the following stored procedure could not be detected"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-29 : 15:14:20

why not populate a variable of XML type inside dynamic sql and make it as output in sp_executesql? then in visual studio create a xml type parameter to receive the returned value

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-29 : 21:20:20

quote:
Originally posted by magmo

This error in Visual Studio "The return type for the following stored procedure could not be detected"

That sounds as though you are trying to use the return value of the stored procedure to send the XML to the .Net code. Return codes have to integers.

To send the XML data to client code you can use a parameter specified as OUTPUT in the stored proc and ParameterDirection set as OUTPUT or INPUTOUTPUT in the .Net code.

Alternatively, you can generate result set (such as you are doing now) and read that data in the .net code.

If you like, post the code fragments from your .Net code and your stored procedure so someone can look at the interfaces you are using.

magmo
Aged Yak Warrior

558 Posts

Posted - 2012-08-29 : 23:54:24

quote:
Originally posted by sunitabeck

quote:
Originally posted by magmo

This error in Visual Studio "The return type for the following stored procedure could not be detected"
That sounds as though you are trying to use the return value of the stored procedure to send the XML to the .Net code. Return codes have to integers.

To send the XML data to client code you can use a parameter specified as OUTPUT in the stored proc and ParameterDirection set as OUTPUT or INPUTOUTPUT in the .Net code.

Alternatively, you can generate result set (such as you are doing now) and read that data in the .net code.

If you like, post the code fragments from your .Net code and your stored procedure so someone can look at the interfaces you are using.

I don't really have any .net code yet since I get this error when I add the sp to the dbml file.

magmo
Aged Yak Warrior

558 Posts

Posted - 2012-08-29 : 23:57:28

quote:
Originally posted by visakh16

why not populate a variable of XML type inside dynamic sql and make it as output in sp_executesql? then in visual studio create a xml type parameter to receive the returned value

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

I don't know how you mean or how to accomplish that :(

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-30 : 01:22:50

quote:
Originally posted by magmo

quote:
Originally posted by visakh16

why not populate a variable of XML type inside dynamic sql and make it as output in sp_executesql? then in visual studio create a xml type parameter to receive the returned value

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

I don't know how you mean or how to accomplish that :(

see an example here

http://www.c-sharpcorner.com/UploadFile/rohatash/get-out-parameter-from-a-stored-procedure-in-Asp-Net/

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

magmo
Aged Yak Warrior

558 Posts

Posted - 2012-08-30 : 01:37:24

quote:
Originally posted by visakh16

quote:
Originally posted by magmo

quote:
Originally posted by visakh16

why not populate a variable of XML type inside dynamic sql and make it as output in sp_executesql? then in visual studio create a xml type parameter to receive the returned value

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

I don't know how you mean or how to accomplish that :(

see an example here

http://www.c-sharpcorner.com/UploadFile/rohatash/get-out-parameter-from-a-stored-procedure-in-Asp-Net/

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ok I see what you mean, that will probably work, a datareader will propably also work, I guess I'll have to go back to that rather than trying to use linq in this particular case...

magmo
Aged Yak Warrior

558 Posts

Posted - 2012-08-30 : 09:20:12

I now tried this approach, I added this part..

@ReturnXml nvarchar(max) OUTPUT

SET @ReturnXml = CONVERT(nvarchar(max), @MyOutPut)

But I get this error when I run this "Implicit conversion from data type xml to nvarchar(max) is not allowed. Use the CONVERT function to run this query."

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-30 : 09:30:19

quote:
Originally posted by magmo

I now tried this approach, I added this part..

@ReturnXml nvarchar(max) OUTPUT

SET @ReturnXml = CONVERT(nvarchar(max), @MyOutPut)

But I get this error when I run this "Implicit conversion from data type xml to nvarchar(max) is not allowed. Use the CONVERT function to run this query."

That error is not coming from the lines of code you posted. Does it also give you a line number on which the error is happening? If it does, examine those lines of code in the stored proc.

The reason I say that the error is not coming from the code you posted is because this runs without any error:

DECLARE @MyOutPut XML = '<root>a</root>';
DECLARE @ReturnXml nvarchar(max);
SET @ReturnXml = CONVERT(NVARCHAR(MAX),@MyOutPut);

magmo
Aged Yak Warrior

558 Posts

Posted - 2012-08-30 : 09:53:25

weird... when I run your code I get this error..

Must declare the scalar variable "@MyOutPut".

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-30 : 09:58:08

quote:
Originally posted by magmo

weird... when I run your code I get this error..

Must declare the scalar variable "@MyOutPut".

That would be the error message if you selected only the second and third lines and ran it. Could that be the case?

magmo
Aged Yak Warrior

558 Posts

Posted - 2012-08-30 : 10:05:26

No...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-30 : 10:29:39

quote:
Originally posted by magmo

I now tried this approach, I added this part..

@ReturnXml nvarchar(max) OUTPUT

SET @ReturnXml = CONVERT(nvarchar(max), @MyOutPut)

But I get this error when I run this "Implicit conversion from data type xml to nvarchar(max) is not allowed. Use the CONVERT function to run this query."

why cant you return it as xml itself? why go for unwanted conversion in between?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-30 : 10:31:22

quote:
Originally posted by magmo

No...

whats type of output variable inside the procedure?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-30 : 10:31:23

Ugh! My bad!! Declaring and initializing a variable in one statement is allowed only on SQL 2008 or later. I forgot that you are on SQL 2005. This should work:

DECLARE @MyOutPut XML;
SET @MyOutPut = '<root>a</root>';
DECLARE @ReturnXml nvarchar(max);
SET @ReturnXml = CONVERT(NVARCHAR(MAX),@MyOutPut);

magmo
Aged Yak Warrior

558 Posts

Posted - 2012-08-30 : 10:33:01

Yes that works

magmo
Aged Yak Warrior

558 Posts

Posted - 2012-08-30 : 10:51:32

quote:
Originally posted by visakh16

quote:
Originally posted by magmo

I now tried this approach, I added this part..

@ReturnXml nvarchar(max) OUTPUT

SET @ReturnXml = CONVERT(nvarchar(max), @MyOutPut)

But I get this error when I run this "Implicit conversion from data type xml to nvarchar(max) is not allowed. Use the CONVERT function to run this query."

why cant you return it as xml itself? why go for unwanted conversion in between?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

I also tried to change it to type xml, the stored procedure itself is parsed ok, but when I try to run it I get this error..


Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'EXEC'.
Msg 137, Level 15, State 2, Line 8
Must declare the scalar variable "@ReturnXml".
Msg 137, Level 15, State 2, Line 10
Must declare the scalar variable "@ReturnXml".

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources