Execute(String) - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Execute(String)

Author

Topic

yellowyellow
Starting Member

3 Posts

Posted - 2010-03-09 : 08:00:28

I have the following procedure

CREATE PROC prcGetPrice
@ProductName varchar(40)
AS
DECLARE @strQuery VARCHAR(MAX)
SET @strQuery= 'SELECT UnitPrice FROM PRODUCTS WHERE PRODUCTNAME=''' + @ProductName + ''''
EXECUTE(@strQuery);
GO

When you execute this procedure it will return UnitPrice of the given ProductName.

My requirement is i want to store that UnitPrice to another variable, something like output parameter.

How to do this?

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-03-09 : 08:21:18

Why you want to go by dynamic if it easy to get it by static query.

CREATE PROC prcGetPrice
@ProductName varchar(40),
@OutUnitPrice AS NUMERIC(18,2) = NULL OUTPUT
AS
SELECT @UnitPrice FROM PRODUCTS WHERE PRODUCTNAME = @ProductName
GO

DECLARE @OutUnitPrice AS Numeric(18,2)
EXEC prcGetPrice 'abc', @OutUnitPrice OUTPUT
SELECT @OutUnitPrice

Vabhav T

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-09 : 08:22:43

why are you using Dynamic SQL ? You can just do it with simple select query


CREATE PROC prcGetPrice
    @ProductName varchar(40)
AS
DECLARE @strQuery VARCHAR(MAX)
        @UnitPrice decimal(10,2)
SET @strQuery= 'SELECT UnitPrice FROM PRODUCTS WHERE PRODUCTNAME=''' + @ProductName + ''''
EXECUTE(@strQuery);

select @UnitPrice = UnitPrice
from   PRODUCTS
where  PRODUCTNAME = @ProductName

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-10 : 13:03:44

see
http://www.sqlteam.com/article/stored-procedures-returning-data

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

yellowyellow
Starting Member

3 Posts

Posted - 2010-03-10 : 23:23:54

Dear Friend, you are right. i know this also. i have different situation. just i put the small example. how to get the value from the dynamic sql execution. i am in a situation to pass the server name dynamically. so i have to use dynamic sql.

hope you understand my situation

thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-10 : 23:27:21

use sp_executesql

read The Curse and Blessings of Dynamic SQL

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-11 : 11:41:14

quote:
Originally posted by yellowyellow

Dear Friend, you are right. i know this also. i have different situation. just i put the small example. how to get the value from the dynamic sql execution. i am in a situation to pass the server name dynamically. so i have to use dynamic sql.

hope you understand my situation

thanks

see this example

http://support.microsoft.com/kb/262499

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources