Sp_ExecuteSql

• SQL2016

  SSC Eights!

  Points: 990

  More actions

  September 16, 2008 at 10:30 am

  #197862

  Hi All,

  I need a help from you.

  I wants to store a value in a variable return from sp_executesql statements.

  let's say...

  declare @SpCode INT

  Exec @SpCode = sp_executesql N'Select Top 1 SPCode from Temp';

  Select @SpCode

  I am unable 2 see the result for this..

  but when i am storing in a table, its working fine...but i need to store one single value. i don't need to store this value in a temp table. its taking too much time for me...

  Can you suggest the correct way to handle this??

  Thanks in advance..

  Milu.

• Lowell

  SSC Guru

  Points: 323504

  More actions

  September 16, 2008 at 11:09 am

  #872561

  what you want to do is look at the books online examples for sp_executesql; look specifically at the OUTPUT parameters.

  here's an example directly modified from BOL: I'm selecting an arbitrary id from sysobjects; you could remove the part that I use for a WHERE statement and paste in your sql, or make it so the where statement is WHERE 1 = 1 or WHERE 1197 = 1197 or something similar for testing.

  SET NOCOUNT ON

  DECLARE @IntVariable int;

  DECLARE @SQLString nvarchar(500);

  DECLARE @ParmDefinition nvarchar(500);

  DECLARE @SpCode int;

  SET @IntVariable = 1197; --arbitrary so i don't return a small number

  SET @SQLString = N'Select Top 1 @innerSPCode=id from sysobjects WHERE id > @level';

  SET @ParmDefinition = N'@level int, @innerSPCode int OUTPUT';

  EXECUTE sp_executesql @SQLString, @ParmDefinition, @level = @IntVariable,@innerSPCode=@SpCode OUTPUT;

  SELECT @SpCode;

  Milu (9/16/2008)

  ---

  Hi All,

  I need a help from you.

  I wants to store a value in a variable return from sp_executesql statements.

  let's say...

  declare @SpCode INT

  Exec @SpCode = sp_executesql N'Select Top 1 SPCode from Temp';

  Select @SpCode

  I am unable 2 see the result for this..

  but when i am storing in a table, its working fine...but i need to store one single value. i don't need to store this value in a temp table. its taking too much time for me...

  Can you suggest the correct way to handle this??

  Thanks in advance..

  Milu.

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• SQL2016

  SSC Eights!

  Points: 990

  More actions

  September 17, 2008 at 10:42 am

  #873146

  Thanks,

  I will go through this..

  Thanks.

  milu.:)