Set a variable equal to an Execute?

Question

Post reply

Set a variable equal to an Execute?

Ken at work

Hall of Fame

Points: 3426
More actions
May 25, 2010 at 5:58 am

#221448

I have this Stored Procedure which works well. This with other lines allow me to call the Stored Procedure from the Front End (Access) and make different selections (or counts) while not exposing the table.
But... now I would like to set the result of this to a variable and I have no idea how to accomplish that. I have set variables to select statements before for I'm having trouble here.
Could someone offer some advice?
I would appreciate it,
Thank you
Execute
(
'SELECT
COUNT(dbo.tbl_TCa_Ul.PM_Status)
FROM
dbo.tbl_WE
Left JOIN dbo.tbl_TCa_Ul
ON dbo.tbl_TC_Ul_PR.WE = dbo.tbl_WkE_PR.dt_WE_Current
WHERE
('
+ '(dbo.tbl_TCa_Ul.Empl_ID = '+ @strEmpl_ID + ') '
+ 'AND (dbo.tbl_TCa_Ul.PM_Status = '+ @strValues + ') '
+ ')'
)

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 1

You can do it by using sp_executesql instead of "Execute".

sp_executesql allows to specify output parameters.

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 2

As elutin points out, sp_executesql will do the trick. Additionally, it virtually defeats any attempt at SQL Injection when done properly and if the code is public facing.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 3

Small example depicting the retrieval of a value from the dynamics-sql to the calling code:

The dynamic-sql take a divisor from the outside, calculates the division and returns the quotient to the outside variable!

Code:

DECLARE @IntVariable int;

DECLARE @SQLString nvarchar(500);

DECLARE @ParmDefinition nvarchar(500);

DECLARE @value INT;

SET @IntVariable = 5;

SET @SQLString = N'SELECT @value_OUT = 10/@Variable ';

SET @ParmDefinition = N'@Variable INT, @value_OUT varchar(30) OUTPUT';

EXECUTE sp_executesql @SQLString, @ParmDefinition, @Variable = @IntVariable, @value_OUT=@value OUTPUT;

SELECT @value;

Hope this helps you!

Ken at work Hall of Fame Points: 3426 More actions · Answer 4

This is great! Thank you for your help.

It even makes creating the SQL statement itself easier.

Thank you again.

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 5

Wow, nice that you understood the concept and appreciating it! Cool! 😎

Cheers, Ken!