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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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
22864 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
2886 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  
 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.07 seconds. Powered By: Snitz Forums 2000