stored procedure

24 Aug 2006 4:54 PM

Hammou

Hello,

I need to create a stored procedure like this :

CREATE PROCEDURE croix
@var1 varchar(50),
@var2 varchar(50)
AS
SELECT @var1,@var2,sum(myvar) as nb
from mytable
group by @var1,@var2
GO

I receive an error.

What is the problem ?

Thanks.

24 Aug 2006 4:55 PM

SQL Menace

you can not do it like that

Use sp_executesql oe exec() if you need to do this dynamically
First read this (http://www.sommarskog.se/dynamic_sql.html) to see
some of the risks

Denis the SQL Menace
http://sqlservercode.blogspot.com/

Hammou wrote:
Show quote

> Hello,
>
> I need to create a stored procedure like this :
>
>
> CREATE PROCEDURE croix
> @var1 varchar(50),
> @var2 varchar(50)
> AS
> SELECT @var1,@var2,sum(myvar) as nb
> from mytable
> group by @var1,@var2
> GO
>
>
> I receive an error.
>
> What is the problem ?
>
>
> Thanks.

24 Aug 2006 4:57 PM

Aaron Bertrand [SQL Server MVP]

> I receive an error.

Could you be more specific? What error?

> SELECT @var1,@var2,sum(myvar) as nb
> from mytable
> group by @var1,@var2

You cannot include variables in the GROUP BY. The GROUP BY is unnecessary
anyway, because SQL Server will understand the variables are constants.

SELECT @var1,@var2,sum(myvar) as nb
from mytable

A

24 Aug 2006 4:58 PM

Aaron Bertrand [SQL Server MVP]

> I need to create a stored procedure like this :
>
>
> CREATE PROCEDURE croix
> @var1 varchar(50),
> @var2 varchar(50)
> AS
> SELECT @var1,@var2,sum(myvar) as nb
> from mytable
> group by @var1,@var2

Are you trying to name the columns dynamically? It's not really clear what
you are trying to accomplish.

28 Aug 2006 2:30 PM

Hammou

Aaron Bertrand [SQL Server MVP] a écrit :
Show quote

>> I need to create a stored procedure like this :
>>
>>
>> CREATE PROCEDURE croix
>> @var1 varchar(50),
>> @var2 varchar(50)
>> AS
>> SELECT @var1,@var2,sum(myvar) as nb
>> from mytable
>> group by @var1,@var2
>
> Are you trying to name the columns dynamically? It's not really clear what
> you are trying to accomplish.
>
>

Thanks, the precedent response gived me the solution.