Solved

output parameter with sp_executesql

Posted on 2006-10-26
9
1,435 Views
Last Modified: 2012-05-05
Hi Experts,

Is it possible to store the results of a call to exec sp_executesql in a parameter.  The parameter is the output for the stored procedure that calls the sp_executesql.

Something like this :-

@result nvarchar(1000),
@nSql nvarchar(100)

set @nSql = 'select * from employees where employeeID = @EmpID' --returns a single record
--@EmpID is a passed in value
set @result = exec sp_executesql @nsql
 --@result is an output parameter for this stored procedure

Regards,

Pete
                        
0
Comment
Question by:Pete_Burke
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +2
9 Comments
 
LVL 23

Expert Comment

by:adathelad
ID: 17812330
Hi,
No, you can't store a result set in a variable like this. What is it your trying to do? Do you have another stored procedure that needs to use the results returned from this stored proc for something? If so, you can load the results into a temp table within that 2nd stored proc and use the values from there:
CREATE PROC prNewProc
AS
CREATE TABLE #results
(EmployeeId INTEGER,
Forename VARCHAR(50),
.. etc
)

INSERT #Results
EXECUTE prGetEmployeeStoredProc 1

-- Now results are in #Results table for use
...
...
DROP TABLE #Results
GO
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17812346
this is the way you can get info back:
@result nvarchar(1000),
@nSql nvarchar(100)

set @nSql = 'select  @res = somefield from employees where employeeID = @EmpID' --returns a single record
exec sp_executesql @nsql, N'@EmpID int, @res nvarchar(1000) OUTPUT', 30, @result OUTPUT



I guess that you are aware that:
select  @result = somefield from employees where employeeID = @EmpID

will work easier?
0
 

Author Comment

by:Pete_Burke
ID: 17812408
I need to select all the columns and I don't want to type in all the cloumn names as I have to do this for 100+ tables hence the 'select * from employees where employeeID = @EmpID'
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 7

Expert Comment

by:assyst
ID: 17812429
try this
---------------------------------------

@result nvarchar(1000),
@nSql nvarchar(100)

set @nSql = 'select * from employees where employeeID =' + @EmpID      --returns a single record
--@EmpID is a passed in value
set @result = exec (@nsql)
0
 
LVL 7

Expert Comment

by:assyst
ID: 17812434
@result nvarchar(1000),
@nSql nvarchar(100)

set @nSql = 'select * from employees where employeeID =' + @EmpID      --returns a single record
--@EmpID is a passed in value

exec (@nsql)
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17812991
>I need to select all the columns and I don't want to type in all the cloumn names as I have to do this for 100+ tables hence the 'select * from employees where employeeID = @EmpID'
do you want to have all the column names into 1 variable as long string?
then you have NO CHOICE but do a

select field1 + field2 + field3  ... from yourtable

and even for each field that is not of data type varchar you need to convert it to varchar explicitely
also, you probably want a column separator.

if you want to have each column into 1 variable each, again you have NO CHOICE
0
 
LVL 23

Expert Comment

by:adathelad
ID: 17813071
What do you need to do with the data it returns?

Somewhere along the line, you will probably need all the column names.
If it's the typing in of those column names your concerned with and the time associated with doing that, you can run a simple query to produce the list of column names for you (see below) and then just copy/paste the list into your SP.

DECLARE @Cols VARCHAR(8000)
SET Cols = ''

SELECT @Cols = @Cols + COLUMN_NAME + ','
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'YourTableName'

IF (RIGHT(@Cols, 1) = ',')
    SET @Cols = LEFT(@Cols, LEN(@Cols) - 1)

PRINT @Cols


0
 

Author Comment

by:Pete_Burke
ID: 17814963
Hi Experts,

Some back ground information, what I have to do is check about 100 tables in sql server to see if there have been any new records added after a specific date and time. Any changes detected must be reported to a front end GUI written in C#, the records will be displayed in a datagrid and later exported to XML.  Each table has a create date time column (Create_DTTM) So I need to loop through all the 100 table names and check if they have any records created after a certain date and time. If they do then select all of the record (select * from table) together with the column names and add it to some kind of "list", ideally inside sql server to aide performance rather than in the GUI application.  Once all of the tables have been checked display all the records in the GUI and then export to XML.  Any ideas on how to do this efficiently and dynamically using stored procedures or some other sql server method? Any suggestions on some kind of list and views on using a datagrid or some thing else. And any top tips on XML would be appreciated.

Regards,

Pete
0
 
LVL 11

Accepted Solution

by:
rw3admin earned 500 total points
ID: 17815897
this is not a solution, but should give you an idea of how to do this, my query is just doing a select you can change this accordingly, also change 'MyDate' to your desired date but make sure its in quotes.

Select       IDENTITY(int, 1,1) id,
      'Select * from '+sysobjects.Name+' where '+sysColumns.name+'=MyDate' Query
Into #T
from       sysColumns
Inner Join
      sysobjects
ON      sysobjects.ID= sysColumns.ID
where       sysColumns.name='ChangeDate'

Declare @Id int, @Query Varchar(8000)
Select @ID=1
While @ID<=(Select Max(ID) from #T)
Begin
Select      @Query =Query from #T where Id=@ID
Exec (@Query)
Select @Id=@Id+1      
End

0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Job Hung 17 37
8 hour Continual coverage based on Time in and Time Out 9 21
Need to replicate a Log table 4 12
Need to find substring in SQL 3 13
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question