REG: Stored Procedure - SQL Server Forums

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)

REG: Stored Procedure

Author

Topic

sanand7984
Starting Member

9 Posts

Posted - 2008-03-14 : 04:46:26

Please do the needful. If I pass the null values for all the three parameters, it shows an error message. If I pass all the three values or any one of the combination is null, it would show the data.

What I have to modify in the below code.
--------------------
Create procedure TestProc
(
@GroupBy1Field varchar(20) ,
@GroupBy2Field varchar(20),
@GroupBy3Field varchar(20)

)
AS

declare @vcSelectStatement varchar(2000),
@vcGroupByStatement varchar(2000),
@vcSqlStatement varchar(2000)

set @vcSelectStatement =
'select case when ' + @GroupBy1Field + ' is not null then ' + @GroupBy1Field + ' else null end AS g1'
+
',case when ' + @GroupBy2Field + ' is not null then ' + @GroupBy2Field + ' else null end AS g2'
+
',case when ' + @GroupBy3Field + ' is not null then ' + @GroupBy3Field + ' else null end AS g3'

set @vcGroupByStatement =
'GROUP BY case when ' + @GroupBy1Field + ' is not null then ' + @GroupBy1Field + ' else null end '
+
',case when ' + @GroupBy2Field + ' is not null then ' + @GroupBy2Field + ' else null end '
+
',case when ' + @GroupBy3Field + ' is not null then ' + @GroupBy3Field + ' else null end '

set @vcSqlStatement = @vcSelectStatement +
' ,sum(Population) as Population from customer_test (NOLOCK)' +
@vcGroupByStatement

exec (@vcSqlStatement)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-03-14 : 04:52:30

print out the @vcSqlStatement see.

KH
[spoiler]Time is always against us[/spoiler]

sanand7984
Starting Member

9 Posts

Posted - 2008-03-14 : 04:56:08

It doesn't show values. O rows

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-14 : 05:00:15

I can see why you are using dynamic SQL for this, but your implementation is SO wrong.

CREATE PROCEDURE dbo.uspTest
(
	@GroupBy1Field VARCHAR(20),
	@GroupBy2Field VARCHAR(20),
	@GroupBy3Field VARCHAR(20)
) 
AS 

SET NOCOUNT ON

DECLARE	@vcSelectStatement VARCHAR(2000),
	@vcGroupByStatement VARCHAR(2000),
	@vcSqlStatement VARCHAR(2000)

SET	@vcSelectStatement = 'SELECT '

IF @GroupBy1Field IS NOT NULL
	SET	@vcSelectStatement = @vcSelectStatement + QUOTENAME(@GroupBy1Field) + ' AS g1,'

IF @GroupBy2Field IS NOT NULL
	set	@vcSelectStatement = @vcSelectStatement + QUOTENAME(@GroupBy2Field) + ' AS g2,'

IF @GroupBy3Field IS NOT NULL
	set	@vcSelectStatement = @vcSelectStatement + QUOTENAME(@GroupBy3Field) + ' AS g3,'

IF @GroupBy1Field IS NULL AND @GroupBy2Field IS NULL AND @GroupBy3Field IS NULL
	SET	@vcGroupByStatement = ''
ELSE
	SET	@vcGroupByStatement = ' GROUP BY '

IF @GroupBy1Field IS NOT NULL
	SET	@vcGroupByStatement = @vcGroupByStatement + QUOTENAME(@GroupBy1Field) + ','

IF @GroupBy2Field IS NOT NULL
	SET	@vcGroupByStatement = @vcGroupByStatement + QUOTENAME(@GroupBy2Field) + ','

IF @GroupBy3Field IS NOT NULL
	SET	@vcGroupByStatement = @vcGroupByStatement + QUOTENAME(@GroupBy3Field) + ','

IF RIGHT(@vcGroupByStatement, 1) = ','
	SET	@vcGroupByStatement = LEFT(@vcGroupByStatement, LEN(@vcGroupByStatement) - 1)

SET	@vcSqlStatement = @vcSelectStatement + 'SUM(Population) AS Population FROM Customer_Test WITH (NOLOCK)' + @vcGroupByStatement

EXEC	(@vcSqlStatement)

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-14 : 05:05:22

My suggestion above should work for any combination of value/null for the three parameters.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-14 : 05:14:14

Or, depending on your business rules

CREATE PROCEDURE dbo.uspTest
(
	@GroupBy1Field VARCHAR(20),
	@GroupBy2Field VARCHAR(20),
	@GroupBy3Field VARCHAR(20)
) 
AS 

SET NOCOUNT ON

DECLARE	@SQL VARCHAR(8000)

SET	@SQL =
'
SELECT		' + COALESCE(QUOTENAME(@GroupBy1Field), 'NULL') + ' AS g1,
		' + COALESCE(QUOTENAME(@GroupBy2Field), 'NULL') + ' AS g2,
		' + COALESCE(QUOTENAME(@GroupB32Field), 'NULL') + ' AS g3,
		SUM(Population) AS Population
FROM		Customer_Test WITH (NOLOCK)
GROUP BY	' + COALESCE(QUOTENAME(@GroupBy1Field) + ',', '') + '
		' + COALESCE(QUOTENAME(@GroupBy2Field) + ',', '') + '
		' + COALESCE(QUOTENAME(@GroupB32Field) + ',', '')

IF @SQL LIKE '%,'
	SET	@SQL = LEFT(@SQL, LEN(@SQL) - 1)

IF @SQL LIKE '%GROUP BY' + CHAR(9)
	SET	@SQL = LEFT(@SQL, LEN(@SQL) - 9)

EXEC	(@SQL)

E 12°55'05.25"
N 56°04'39.16"

sanand7984
Starting Member

9 Posts

Posted - 2008-03-14 : 05:14:53

If I select null values for the three parameters, it is showing an error.

And If i Select first two parameters and third as null, it showing the same error.

ERROR:
------

Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'Region'. [Error in Second parameter]

quote:
Originally posted by Peso

My suggestion above should work for any combination of value/null for the three parameters.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-14 : 05:19:20

If you write a PRINT @vcSqlStatement just before the EXEC(@vcSqlStatement), what do the output show you?

E 12°55'05.25"
N 56°04'39.16"

sanand7984
Starting Member

9 Posts

Posted - 2008-03-14 : 05:25:58

If I select Null value for all the three, it throws an error like this.

SELECT ,SUM(Population) AS Population FROM Customer_Test WITH (NOLOCK)
Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ','.

quote:
Originally posted by Peso

If you write a PRINT @vcSqlStatement just before the EXEC(@vcSqlStatement), what do the output show you?

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-14 : 05:29:25

Please use last version before writing there is an error.
Try again with the two suggestions above.

E 12°55'05.25"
N 56°04'39.16"

sanand7984
Starting Member

9 Posts

Posted - 2008-03-14 : 05:45:14

It's Working, I tried the first one. Its working perfectly. Thanks a tom=ne Peso

sanand7984
Starting Member

9 Posts

Posted - 2008-03-14 : 05:49:55

Peso, onemore thing how do I get the insert script with data in the table in sql server 2005.

quote:
Originally posted by sanand7984

It's Working, I tried the first one. Its working perfectly. Thanks a ton Peso

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-14 : 05:53:55

Wrap an INSERT statement around the SELECT statement?

Seriously, how do you expect us to answer that question without knowing anything about your requirement and environment?

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources