SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Paramater wtihin Select Statement
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

masonr
Starting Member

4 Posts

Posted - 05/25/2007 :  12:12:38  Show Profile  Reply with Quote
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  Show Profile  Visit nr's Homepage  Reply with Quote
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.
Go to Top of Page

rudesyle
Posting Yak Master

110 Posts

Posted - 05/25/2007 :  12:26:50  Show Profile  Reply with Quote
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.
Go to Top of Page

masonr
Starting Member

4 Posts

Posted - 05/25/2007 :  12:40:10  Show Profile  Reply with Quote
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
Go to Top of Page

masonr
Starting Member

4 Posts

Posted - 05/25/2007 :  12:46:42  Show Profile  Reply with Quote
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)
Go to Top of Page

masonr
Starting Member

4 Posts

Posted - 05/25/2007 :  12:47:29  Show Profile  Reply with Quote
nevermind. sometimes it helps to re-read what you wrote.....
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22713 Posts

Posted - 05/26/2007 :  01:26:09  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
More on dynamic sql
http://www.sommarskog.se/dynamic_sql.html


Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

jezemine
Flowing Fount of Yak Knowledge

USA
2884 Posts

Posted - 05/26/2007 :  03:21:22  Show Profile  Visit jezemine's Homepage  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000