set @var = exec @sql problem - 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

set @var = exec @sql problem

Author

Topic

cembit
Starting Member

5 Posts

Posted - 2008-10-04 : 05:56:55

Hello,

I'm in trouble about dynamic sql queries.

All i wanna do is to get count(*) value. Can somebody help?

Please write code because i'm new to sql and i can't grasp definitions and complex examples. (i'm searching the net, and saw examples that uses temporary tables but i couldn't apply them to my code)

thank you :)

CREATE Procedure dbo.GetAllUrunlerRowCount
(
@Count int OUTPUT,
)
AS

DECLARE @sta1 varchar(128);
DECLARE @sta2 varchar(560);
DECLARE @sta3 varchar(128);
DECLARE @sta4 varchar(64);
DECLARE @sta5 varchar(90);
DECLARE @sta6 varchar(128);
DECLARE @sta7 varchar(64);
DECLARE @sta8 varchar(64);
DECLARE @sta9 varchar(64);
DECLARE @sta10 varchar(90);
DECLARE @sta11 varchar(560);

---here is a preparation code for @sta1 to @sta11

DECLARE @sql nvarchar(4000)

SET @sql = 'SELECT COUNT(*) AS [eben] FROM tUrunler WHERE
'+@sta1+@sta2+@sta3+@sta4+@sta5+@sta6+@sta7+@sta8+@sta9+@sta10+@sta11+'(5=5)'

EXEC sp_executesql @sql

SET @Count = ????????

GO

GRANT EXEC ON GetAllUrunlerRowCount TO PUBLIC

GO

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-04 : 06:36:22

you need to define @Count as output parameter and then return the value through it using sp_executesql. have a look at sp_executesql syntax here

http://msdn.microsoft.com/en-us/library/ms188001.aspx

and see second example where they have returned value through output param.

b/w what was the reason for going for dynamic sql here? can you explain what you're trying to do here please?

cembit
Starting Member

5 Posts

Posted - 2008-10-04 : 12:50:29

Thank you,

I tried but i get the error "Specified cast is not valid."

DECLARE @sql nvarchar(4000)
DECLARE @saydis int
DECLARE @ParmDefinition nvarchar(512)

SET @sql = N'SELECT say = COUNT(*) FROM tUrunler WHERE
'+@sta1+@sta2+@sta3+@sta4+@sta5+@sta6+@sta7+@sta8+@sta9+@sta10+@sta11+'(5=5)'

SET @ParmDefinition = N'@say int OUTPUT';

EXEC sp_executesql @sql, @ParmDefinition, @say = @saydis OUTPUT;
SELECT @saydis
SET @Count = @saydis

[@Count defined as int OUT parameter)

Any other suggestions?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-04 : 13:51:37

quote:
Originally posted by cembit

Thank you,

I tried but i get the error "Specified cast is not valid."

DECLARE @sql nvarchar(4000)
DECLARE @saydis int
DECLARE @ParmDefinition nvarchar(512)

SET @sql = N'SELECT @say = COUNT(*) FROM tUrunler WHERE
'+@sta1+@sta2+@sta3+@sta4+@sta5+@sta6+@sta7+@sta8+@sta9+@sta10+@sta11+'(5=5)'

SET @ParmDefinition = N'@say int OUTPUT';

EXEC sp_executesql @sql, @ParmDefinition, @say = @saydis OUTPUT;
SELECT @saydis
SET @Count = @saydis

[@Count defined as int OUT parameter)

Any other suggestions?

you missed @ for say. b/w i didnt understand the code after WHERE what does @sta1+@sta2+....+'(5=5)' mean?

cembit
Starting Member

5 Posts

Posted - 2008-10-05 : 04:48:32

Oh, thank you! It works now :)

I have created 11 statements which specifies search parameters.

IF @ProductCode IS NOT NULL
SET @sta1 = ' (ProductCode LIKE ''%'+@ProductCodeP+'%'') AND '
ELSE
SET @sta1 = ''

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-05 : 04:51:51

quote:
Originally posted by cembit

Oh, thank you! It works now :)

I have created 11 statements which specifies search parameters.

IF @ProductCode IS NOT NULL
SET @sta1 = ' (ProductCode LIKE ''%'+@ProductCodeP+'%'') AND '
ELSE
SET @sta1 = ''

and you will be passing comma seperated values to them?

cembit
Starting Member

5 Posts

Posted - 2008-10-05 : 05:49:49

yes, user can send comma seperated values. does it open to the sql injection attack?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-05 : 06:10:44

quote:
Originally posted by cembit

yes, user can send comma seperated values. does it open to the sql injection attack?

i think you can achieve all this without use of dynamic sql which minimizes sql injection attack possibilities.
Instead of creating dynamic string like below

CREATE Procedure dbo.GetAllUrunlerRowCount
(
@Count int OUTPUT,
)
AS


DECLARE @sta1 varchar(128);
DECLARE @sta2 varchar(560);
DECLARE @sta3 varchar(128);
DECLARE @sta4 varchar(64);
DECLARE @sta5 varchar(90);
DECLARE @sta6 varchar(128);
DECLARE @sta7 varchar(64);
DECLARE @sta8 varchar(64);
DECLARE @sta9 varchar(64);
DECLARE @sta10 varchar(90);
DECLARE @sta11 varchar(560);

---here is a preparation code for @sta1 to @sta11

DECLARE @sql nvarchar(4000)

IF @ProductCode IS NOT NULL
SET @sta1 = ' (ProductCode LIKE ''%'+@ProductCodeP+'%'') AND '
ELSE 
SET @sta1 = ''

....

SET @sql = 'SELECT COUNT(*) AS [eben] FROM tUrunler WHERE 
'+@sta1+@sta2+@sta3+@sta4+@sta5+@sta6+@sta7+@sta8+@sta9+@sta10+@sta11+'(5=5)'

EXEC sp_executesql @sql

SET @Count = ????????


GO

you could use something like

CREATE Procedure dbo.GetAllUrunlerRowCount
(
@Count int OUTPUT,
)
AS


SELECT @say=COUNT(*) FROM tUrunler 
WHERE (','+ @ProductCode + ',' LIKE '%,' + ProductCode + ',%'
OR @ProductCode IS NULL)AND...other similar conditions

SET @Count = @say

GO

also make sure you cast fields used in WHERE condition to varchar just in case they're not varchar

cembit
Starting Member

5 Posts

Posted - 2008-10-05 : 07:50:37

Ok, i'll try that way..

Thanks for your help. :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-05 : 08:14:03