Paramater wtihin Select Statement

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

SQL Server 2005 Forums

Transact-SQL (2005)

Paramater wtihin Select Statement

Author

Topic

masonr
Starting Member

4 Posts

Posted - 2007-05-25 : 12:12:38

Trying to pass a parameter as a groupby field in a Select statement

Is this possible or feasable?

I’m actually creating a table-valued function that is then called by a stored procedure or another function.

I have the need to do several very similar select statements, just grouping by different fields: (probably 30 different zone types currently)

--State
Select STATECODE, _______, _________
From TableName

--County
Select COUNTYCODE, _________, _________
From TableName

What I have tried so far is this in a stored procedure: With @Zone being text holding either ‘StateCode’, ‘CountyCode’, etc……
My intention is to group the query by whichever @Zone I send to it.

Doesn’t work, just fills in the first field with the string text that I pass to it
Select @Zone, ________, ________
From TableName

In VB I can do this with, but I need to flexibility of the nested stored proc/functions in SQL:

Dim SQLstring as string
Dim Zone as string

Zone = "StateCode"

SQLstring = “Select “ & Zone & “, AcctName, AccountNumber From TableName”

Or does anybody have a better solution besides enjoying memorial day weekend?

nr
SQLTeam MVY

12543 Posts

Posted - 2007-05-25 : 12:22:38

exec ('select ' + @Zone + ', ... from tablename group by ' + @Zone)

The user will need permission on the table and beware of sql injection.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

rudesyle
Posting Yak Master

110 Posts

Posted - 2007-05-25 : 12:26:50

quote:
Originally posted by nr

exec ('select ' + @Zone + ', ... from tablename group by ' + @Zone)

The user will need permission on the table and beware of sql injection.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

use sp_executesql instead and you won't have to worry about sql injection.

masonr
Starting Member

4 Posts

Posted - 2007-05-25 : 12:40:10

Thanks for the help, but ran into 1 more problem.

I get the following error because I'm trying to set it to table datatype for a table-valued function:
error 197: Execute connot be used as a source when inserting into a table variable

Any suggestions? I'm pretty new to Stored Procedures

I now have:

CREATE function Test (@Key int, @zone varchar(100))
returns @t table (
Zone varchar(10), AcctName varchar(40), AcctNum varchar(10), PolicyID varchar(8), Aggregate real)as begin

insert into @t
exec ('select' + @Zone + ', accgrpname, accgrpnum, policyid, dbo.Aggregate(LocIdValueAmt, BLANLIMAMT, UNDCOVAMT, PARTOF from dbo.Qry_10_LocLimits(@Key) group by' + @zone)

return
end

masonr
Starting Member

4 Posts

Posted - 2007-05-25 : 12:46:42

It was working with the following before:

insert into @t
select statecode, accgrpname, accgrpnum, policyid, dbo.Aggregate(LocIdValueAmt, BLANLIMAMT, UNDCOVAMT, PARTOF)
from
dbo.Qry_10_LocLimits(@Key)

masonr
Starting Member

4 Posts

Posted - 2007-05-25 : 12:47:29

nevermind. sometimes it helps to re-read what you wrote.....

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-26 : 01:26:09

More on dynamic sql
http://www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-05-26 : 03:21:22

quote:
Originally posted by rudesyle
use sp_executesql instead and you won't have to worry about sql injection.

you must have meant "use sp_executesql correctly instead and you won't have to worry about sql injection."

simply using sp_executesql instead of EXEC does not protect you. because, after all, you can just do this:


create proc CallMeAtYourOwnRisk
@sql varchar(1000) 
as
exec sp_executesql @sql

you wouldn't call this safe would you?

www.elsasoft.org

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources