August 9, 2010 at 1:20 am
Hi all !!
May my question be of stupidity, but plz let me clarify the possibility. Thanks in advance !!
I wanna know the possibility of passing parameter to Group By function ..
Like after the query,
Group by @Item
kindly bear my innocence.
August 9, 2010 at 2:08 am
If i am understanding your requirement correctly you want to keep the GROUP BY Clause Dynamic. For this you can use a Dynamic Query like given below
DECLARE@strGroupByClause VARCHAR(1000)
DECLARE@strSQL VARCHAR(8000)
SET@strGroupByClause = 'Col1,Col2,Col3' -- List the columns here
SET@strSQL = ' SELECT ' + @strGroupByClause + ', COUNT(*) NumberOfEntries '
+ ' FROM TableName '
+ ' GROUP BY ' + @strGroupByClause
EXECUTE( @strSQL )
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
August 9, 2010 at 2:16 am
No, you can not, unless you are using Dynamic SQL..
If u want to use variables to decide the column u want to aggregate your result set , i can give u an example ; follow this:
declare @table table (col1 char(1) , col2 char(2), amount int )
insert into @table (col1,col2, amount)
select 'A' ,'A', 100
union all select 'B' ,'A', 200
union all select 'C' ,'A', 300
union all select 'D' ,'A', 400
union all select 'E' ,'A', 500
union all select 'A' ,'B', 200
union all select 'B' ,'B', 300
union all select 'C' ,'B', 400
union all select 'D' ,'B', 500
declare @col int
set @col = 1
select
case
when @col = 1 then col1
when @col = 2 then col2
end col_chosen
, sum(amount) sm
from @table
group by
case
when @col = 1 then col1
when @col = 2 then col2
end
August 9, 2010 at 5:23 am
Hey its wonderful tht a solution is being with my assumed condition..
now i have mingle both of your query to make my one to achieve and it was working well with query level.
I tried to make it as procedure but its throwing some error,
here it is
Create Procedure Testing
@grp1 VARCHAR(1000) = '[Tab1SalesHeader].No, [Tab1SalesLine].Description, [Tab1SalesLine].[Variant Code]',
@grp2 VARCHAR(1000) = '[Tab1SalesHeader].No, [Tab1Sales Line].Description',
@SQL Varchar(8000) = ('Select' + Case When @Col = 1 Then @grp1 When @Col = 2 Then @grp2 end +
', Convert(numeric(10,2), SUM([Tab1SalesLine].Quantity)) AS EXPR1' + ',Count(*)' + 'From
[Tab1SalesHeader] INNER JOIN [Tab1SalesLine] ON [Tab1SalesHeader].No = [Tab1SalesLine].[No]' +
'Group By' + Case When @Col = 1 Then @grp1 When @Col = 2 Then @grp2 end)
@Col int
As
Execute (@SQL)
Can u plz tell me where its going wrong??
Thanks a lot...
August 9, 2010 at 5:46 am
Your logic is correct. Can you post the exact error, so that we can help you
August 9, 2010 at 5:52 am
Oh sure Thanks,
The error is as below
Msg 102, Level 15, State 1, Procedure Testing, Line 4
Incorrect syntax near '('.
Msg 137, Level 15, State 2, Procedure Testing, Line 9
Must declare the scalar variable "@SQL".
August 9, 2010 at 6:05 am
i have done little modification
Create Procedure Testing
AS
DECLARE @SQL VARCHAR(MAX)
DECLARE @grp1 VARCHAR(1000)
DECLARE @grp2 VARCHAR(1000)
DECLARE @Col VARCHAR(10)
DECLARE @SELECTLIST VARCHAR(MAX)
SET @Col= 2
SET @grp1= '[Tab1SalesHeader].No, [Tab1SalesLine].Description, [Tab1SalesLine].[Variant Code]'
SET @grp2= '[Tab1SalesHeader].No, [Tab1Sales Line].Description'
IF (@Col=1)
SET @SELECTLIST = @grp1
ELSE
SET @SELECTLIST = @grp2
SET @SQL = 'Select ' +@SELECTLIST +'
, Convert(numeric(10,2), SUM([Tab1SalesLine].Quantity)) AS EXPR1,Count(*) From
[Tab1SalesHeader] INNER JOIN [Tab1SalesLine] ON [Tab1SalesHeader].No = [Tab1SalesLine].[No]' +
'Group By ' + @SELECTLIST
EXEC(@SQL)
Thanks
R.Vasanth
August 9, 2010 at 6:44 am
Vasanth !! Extraordinary !!! It worked !!
When i executed no error was thrown and the procedure was created.
Actually i wish to pass the Col value as parameter that the user should enter the value of the Col field..
Can you plz say me how could i do that ??
Thanks a million for helping out !!!
August 9, 2010 at 6:46 am
Create Procedure Testing
(
@Col int
)
AS
DECLARE @SQL VARCHAR(MAX)
DECLARE @grp1 VARCHAR(1000)
DECLARE @grp2 VARCHAR(1000)
//DECLARE @Col VARCHAR(10)
DECLARE @SELECTLIST VARCHAR(MAX)
//SET @Col= 2
SET @grp1= '[Tab1SalesHeader].No, [Tab1SalesLine].Description, [Tab1SalesLine].[Variant Code]'
SET @grp2= '[Tab1SalesHeader].No, [Tab1Sales Line].Description'
IF (@Col=1)
SET @SELECTLIST = @grp1
ELSE
SET @SELECTLIST = @grp2
SET @SQL = 'Select ' +@SELECTLIST +'
, Convert(numeric(10,2), SUM([Tab1SalesLine].Quantity)) AS EXPR1,Count(*) From
[Tab1SalesHeader] INNER JOIN [Tab1SalesLine] ON [Tab1SalesHeader].No = [Tab1SalesLine].[No]' +
'Group By ' + @SELECTLIST
EXEC(@SQL)
August 10, 2010 at 12:40 am
Hi ! Thanks for your help !!
Again now i need to pass a date parameter too, so i did modified the procedure still as below,
Create Procedure Testing
(
@Date Datetime
@Col int
)
AS
DECLARE @SQL VARCHAR(MAX)
DECLARE @grp1 VARCHAR(1000)
DECLARE @grp2 VARCHAR(1000)
//DECLARE @Col VARCHAR(10)
DECLARE @SELECTLIST VARCHAR(MAX)
//SET @Col= 2
SET @grp1= '[Tab1SalesHeader].No, [Tab1SalesLine].Description, [Tab1SalesLine].[Variant Code]'
SET @grp2= '[Tab1SalesHeader].No, [Tab1Sales Line].Description'
IF (@Col=1)
SET @SELECTLIST = @grp1
ELSE
SET @SELECTLIST = @grp2
SET @SQL = 'Select ' +@SELECTLIST +'
, Convert(numeric(10,2), SUM([Tab1SalesLine].Quantity)) AS EXPR1,Count(*) From
[Tab1SalesHeader] INNER JOIN [Tab1SalesLine] ON [Tab1SalesHeader].No = [Tab1SalesLine].[No]
Where [Tab1SalesHeader].OrderDate <'+IsDate(@Date) +
'Group By ' + @SELECTLIST
EXEC(@SQL)
But on Execution :
EXEC Testing '20100101', 2
gives error like,
Conversion failed when converting the varchar value 'Select[Tab1SalesHeader].No, [Tab1Sales Line].Description, SUM([Tab1SalesLine].Quantity)) AS EXPR1,Count(*) From
[Tab1SalesHeader] INNER JOIN [Tab1SalesLine] ON [Tab1SalesHeader].No = [Tab1SalesLine].[No]
Where [Tab1SalesHeader].OrderDate <' to data type int
how to overcome this ?? Thanks for your kind help !!
August 10, 2010 at 12:43 am
Do casting
i,e <' Cast(date, varchar(25))
August 10, 2010 at 1:25 am
To be precise
CAST (date AS VARCHAR)
-- OR
CONVERT (date , VARCHAR)
August 10, 2010 at 3:33 am
Please any one reply !!!
August 10, 2010 at 3:36 am
Sorry for the previous message..
Now when executing its throwing,
Exec Testing '20100101', 1
Msg 102, Level 15, State 1, Line 6
Incorrect syntax near '1'.
August 10, 2010 at 3:50 am
Please can we see your stored procedure definition?
Thanks
John
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply