Return Value From Stored Procedure 1

[PGO01]

Here's a simple stored procedure:

CREATE PROCEDURE MyProc
AS
BEGIN
    DECLARE @Query nvarchar(max)
    SET @Query = 'SELECT 123'
    EXEC(@Query)
END

Now the following would show '123' in the result window:

EXEC MyProc

But I want to set the output of this stored procedure to another variable:

DECLARE @RetVal nvarchar(max)
EXEC @RetVal = EXEC MyProc
SELECT @RetVal

This doesn't work! Anyone know what I am doing wrong?

 

[bborissov]

Use OUTPUT parameter for this:

CREATE PROCEDURE MyProc (@MyRetVal varchar(max) OUTPUT)
AS
BEGIN
    DECLARE @Query nvarchar(max)
    SET @Query = 'SELECT 123'
    EXEC(@Query)
    SET @MyRetVal = 'What I want to return from my SP?'
END

Now:

DECLARE @RetVal varchar(max)
EXEC MyProc @MyRetVal = @RetVal OUTPUT
SELECT @RetVal

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[PGO01]

What I want returned is the result of the execution of the dynamic sql. i.e. the result from EXEC(@Query).

In this example, 123.

How do I get that?

 

[SQLSister]

You are only returning one field in your select? Then set the value of the select to an output variable in your stored proc and return an output variable. If you are returning multiple fields, then createa a temp table (or in newer versions of SQL server you can use a table variable in you calling proc and then insert into that table using the insert comaand, something like:

create table #temp (field1 varchar(10), id int)

insert into #temp
exec myProc

Why are you using dynamic sql to begin with, this is anti-pattern in t-sql and should be voided if at all possible. If you feel you need to use it, you probably have a design problem that needs to be fixed or you are trying to generalize too much which is a very bad thing in t-sql as it leads to major performance problems.

"NOTHING is more important in a database than integrity." ESquared

 

[PGO01]

Hi SQLSister,

I am always returning 1 value yes.

How do I "set the value of the select to an output variable in your stored proc" considering I am using dynamic SQL?

All I can do is EXEC(@myQuery) which doesn't assign the result to anything - just executes it.

I need something like SET @myVar = EXEC(@myQuery).

 

[markros]

Can you please post a bit more regarding your dynamic SQL? Perhaps it's not needed in your case at all.

If you want to be able to create a variable and use it
as

declare @myCount int

select @myCount = count(*) from myTable where myCondition

(the select statement being a dynamic SQL) then look at sp_ExecuteSQL procedure in BOL.

See these two blogs for discussion

http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/changing-exec-to-sp_executesql-doesn-t-p

http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/avoid-conversions-in-execution-plans-by-

 

[PGO01]

Sure, I'm always willing to change the process right from scratch if it makes it better. Here's a run down of what I'm doing:

1. I have triggers on my tables that write the inserted and deleted valus to a service broker queue whenever an insert/update/delete occurs.

2. The queue is processed by a stored procedure which writes the values to an audit table.

3. The Audit table holds the old and new values in the form of Inserted_Phone, Deleted_Phone, Inserted_Fax, Deleted_Fax, etc. Also held in the audit table is when the action occurred and whether it was an insert, update or delete.

4. I now need to create a view on the audit table that shows only those values that have actually changed. This could be all columns, or it could be just 1 out of 50 or 60.

5. This info is then to be built up in a report and emailed out - but I've got that side of things sorted.

Any ideas on how to best do this then?

 

[bborissov]

On top off my head:
Create a table:
[tt]
audit table
TableName FieldName OldValue NewValue ChangedDate
varchar(100) varchar(100) varchar(max) varchar(max) datetime
--------------------------------------------------------------
[/tt]

Then instead of having 50-60 fields for every field in table you will have 50-60 records

Then all queries will be easier.
In your trigger you must just add records for changed fields, name of the table (yes you could use this audit table for many tables) field name of the changed field and cast everything to varchar.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[markros]

In the above structure we may also want to add WhoChanged column.

I like this idea - sounds like the right structure to implement.