Sp_executesql @stat

Question

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 .

Answer 1

0

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.

answered Jul 18 '12 at 06:12 PM

SirSQL
4.6k ● 1 ● 3

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 '12 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 '12 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 '12 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 '12 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 '12 at 07:58 AM Usman Butt

show 2 more replies

(comments are locked)

Answer 2

0

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]  
 go

 CREATE PROCEDURE p_Test
 AS 
 SELECT TOP 5
 ROW_NUMBER() OVER ( PARTITION BY [c].[column_id] ORDER BY [c].[column_id] ) ,
 'Test'
 FROM    [sys].[columns] AS c
 GO

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

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

 SELECT  *
 FROM    @table AS t

 go

 DROP PROC p_test

answered Jul 21 '12 at 10:16 PM

Fatherjack ♦♦
38.8k ● 55 ● 69 ● 104

(comments are locked)

Sp_executesql @stat

Follow this question

Related Questions