| Author |
 Topic  |
|
|
masonr
Starting Member
4 Posts |
 Posted - 05/25/2007 : 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
United Kingdom
12543 Posts |
Posted - 05/25/2007 : 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 - 05/25/2007 : 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 - 05/25/2007 : 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 - 05/25/2007 : 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 - 05/25/2007 : 12:47:29
|
| nevermind. sometimes it helps to re-read what you wrote..... |
|
|
|
madhivanan
Premature Yak Congratulator
India
22461 Posts |
|
|
jezemine
Flowing Fount of Yak Knowledge
USA
2871 Posts |
Posted - 05/26/2007 : 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 |
|
|
| |
Topic |
|
Paramater wtihin Select Statement
