Using variable in stored procedure

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)

Using variable in stored procedure

Author

Topic

bi89405
Starting Member

35 Posts

Posted - 2010-08-26 : 15:04:03

I am trying to create a stored procedure that uses a variable as a parameter.

The code looks something like this:


CREATE PROCEDURE apples
@quarter varchar(2) AS
select name, value,
CASE
   WHEN @quarter='Q1' THEN
(select sum(value) where some condition) 'M1'
from
(select * from some condition) a
LEFT OUTER JOIN
(select * from some condition) b ON a.id=b.id
LEFT OUTER JOIN
table c ON b.id=c.id
GO

EXEC apples @quarter='Q1'
GO

The stored procedure does not get created and I am getting a few of error messages that says error near 'M1, near 'a', near 'b', and could not find stored procedure 'apples'

Please help me, what am I doing wrong?

TIA,
Zack

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-08-26 : 15:08:08

The syntax is sooo wrong.
Can you explain what you want to do and give structure, sample data and wanted output?

No, you're never too old to Yak'n'Roll if you're too young to die.

bi89405
Starting Member

35 Posts

Posted - 2010-08-26 : 15:11:21

Just a note:

The following query works fine if I take out the case statement:


select name, value,
(select sum(value) where some condition) 'M1'
from
(select * from some condition) a
LEFT OUTER JOIN
(select * from some condition) b ON a.id=b.id
LEFT OUTER JOIN
table c ON b.id=c.id

I will ultimately have the user enter Q1, Q2, Q3, and Q4 for quarterly information. So column name M1 in this case is just a test to see if Quarter 1 returns the desired data. What can I do to correct my code?

Thanks,
Zack

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-08-26 : 15:18:43

Do you have a SELECT that REALLY works? I mean a query without regarding a special quarter?
If yes, then please show us this query and maybe we can tune it up to have a condition for a given quarter.
But with the actual given information it is not possible to help.

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-08-26 : 15:19:44

And please don't say this:

quote:
select name, value,
(select sum(value) where some condition) 'M1'
from
(select * from some condition) a
LEFT OUTER JOIN
(select * from some condition) b ON a.id=b.id
LEFT OUTER JOIN
table c ON b.id=c.id

is working fine!

No, you're never too old to Yak'n'Roll if you're too young to die.

bi89405
Starting Member

35 Posts

Posted - 2010-08-26 : 15:28:12

Ok, here is the full query,...that works.


select left(a.value,12) 'num', a.join, c.last+', '+c.first 'managername',
((select sum(tamount) from work where a.join=case and 
tdate >= '12/01/2009' and
tdate < '03/01/2010')+ 
(select sum(camount) from expense where a.join=case and  
cdate >= '12/01/2009' and
cdate < '03/01/2010')) 'M1'
from
(select *
from udf 
where value is not null and utype='MT' and udfindex='11') a

LEFT OUTER JOIN

(select *
from udf 
where value is not null and utype='MT' and udfindex='51') b ON a.join=b.join

LEFT OUTER JOIN

employee c ON b.value=id

How can I add a stored procedure that takes the last column and based on the input by the user, it will calculate for that column.

Make sense?

bi89405
Starting Member

35 Posts

Posted - 2010-08-26 : 15:35:34

Nevermind, I realized that all I had to add was END AS 'M1' rather than simply 'M1'. Going back to your first reply to my post, the query was not SOOO wrong if you paid closer attention.

Thanks anyway.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources