Sp_executesql @stat

 Set @stat= 'SELECT spid, kpid from sys.sysprocesses '

 Sp_executesql @stat

The above statement's o/p i need it in varaible like @spid , @Kpid . Imp :- i need both SPID as well as KPID as output parameter

Please help .

more ▼

asked Jul 18, 2012 at 06:06 PM in Default

avatar image

110 29 30 36

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

I strongly recommend that you read books online which has excellent examples of how to use dynamic parameters with sp_executesql. You can find this reference at http://msdn.microsoft.com/en-us/library/ms188001.aspx Trying to help you to fish rather than just give you code. If you still run into troubles, post some updated code.

more ▼

answered Jul 18, 2012 at 06:12 PM

avatar image

4.9k 4 5

Thanks for your comment. I have already gone thought the link , and i am able to get a single output for my given query ie 'SELECT kpid from sys.sysprocesses . But i need the o/p for two column which is the palce i have really stuck up .

Jul 18, 2012 at 06:15 PM jhunu

Are you trying to dump the entire result set from sysprocesses into a variable? And to return it how? And to use it how? A little clarity around your reasoning on this might be useful. I'm also curious as to why you would be using sysprocesses (unless this is for SQL 2000)

Jul 18, 2012 at 07:03 PM SirSQL

@SirSQL +1 for making the OP really works for the solution instead of spoon feeding.

As far as sysprocesses is concerned, it still have its usage. For instance Kalen Delaney: Why I still need Sysprocesses

Jul 19, 2012 at 05:56 AM Usman Butt

SirSQL ,

Sysprocesses is just an example of a table . It can be any table . yes , I am trying to dump the 3 columns Output from a table into a 3 Variables .

EG - Set @stat= 'SELECT @sp=spid, @KP kpid from sys.sysprocesses ' For one column its working fine , but for more than one column its not working dynamically . For more than one column i need to insert the output of the select statement into a Temp table , which is what i dont want . I want the Output to be in a varaibles itself .

Jul 19, 2012 at 06:47 AM jhunu

@jhunu If there are more than one rows in the table, then this variable assignment is not the correct way. You then need to insert the data into a temporary table. Otherwise, the variable would hold only the last value assigned.

Moreover, post your fully executable code to get better help

Jul 19, 2012 at 07:58 AM Usman Butt
(comments are locked)
10|1200 characters needed characters left

If you create a table first you can then insert the results of a stored procedure or other code called by EXEC or sp_executesql into it

  USE [AdventureWorks]  

  ROW_NUMBER() OVER ( PARTITION BY [c].[column_id] ORDER BY [c].[column_id] ) ,
  FROM    [sys].[columns] AS c

  DECLARE @table TABLE ( ID INT, Words CHAR(5) )

 INSERT  INTO @table
  ( [ID], [Words] )
  EXEC p_Test

  FROM    @table AS t


  DROP PROC p_test
more ▼

answered Jul 21, 2012 at 10:16 PM

avatar image

Fatherjack ♦♦
43.8k 79 99 118

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Jul 18, 2012 at 06:06 PM

Seen: 1721 times

Last Updated: Jul 21, 2012 at 10:18 PM

Copyright 2017 Redgate Software. Privacy Policy