Please start any new threads on our new site at 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
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Procedure with parameter
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

2 Posts

Posted - 11/28/2012 :  09:07:50  Show Profile  Reply with Quote
Hi all,
I've been trying to write a procedure that uses the parameter in the name of several variables and a table, but do not know the right syntax. It goes something like this:

Coalesce(sum(f2), 0) as ctr@year,
--a few more similar lines
INTO dbo.T3@year

FROM T1 t1, T2 t2
Where f3 >= f4-10000*@year AND
f3 < f5-10000*@year
group by T2.f1

Obviously, the above code does not work the way I want it to (it does work in SAS, where the original code was written).

So, question 1: how can I make it work?
question 2: is there a better, more elegant/efficient way to accomplish the same task?

To give you more details, the fields f3, f4 and f5 above are dates as integers, and what I need is some totals over each of 3 prior years, later to be all added to the same table, indexed by f1.
Sorry for the long post!

Flowing Fount of Yak Knowledge

8781 Posts

Posted - 11/28/2012 :  10:18:13  Show Profile  Visit webfred's Homepage  Reply with Quote
See here:

Too old to Rock'n'Roll too young to die.
Go to Top of Page

Flowing Fount of Yak Knowledge

4614 Posts

Posted - 11/28/2012 :  11:31:38  Show Profile  Reply with Quote
I must be missing someting as I don't see why this needs to be dynamic sql. Can you post DDL, DML and expected output, that will help to hel you better. If you are unfamilar with how to do that, here are some links that can help:
Go to Top of Page

Starting Member

2 Posts

Posted - 11/28/2012 :  14:04:03  Show Profile  Reply with Quote
Thanks for the link, webfred, it's good read! I believe that I could make it work, but maybe there is an easier way?

So, here is a simplified way to look at my problem. I have a table as below, where I want to summarize by id several variables (C1-C8, L1-L8). Now, I would like to do it for each of several years back (3 for now, but let's say n in general), which is where my "ldate between effdate-10000*@n and expdate-10000*@n" is needed. How can I do this with a single procedure that creates a table with unique id and C1_P1, C1_P2,..., C1_Pn, L1_P1...L1_Pn as field names?
Hope this makes sense.

create table dbo.mytable (id int, effdate int, expdate int, ldate int, C1 int, C2 int)

insert into dbo.mytable 

(id, effdate, expdate, ldate, C1, C2)

 SELECT '29','20101017','20110417','20110305','0','1' UNION ALL
SELECT '29','20101017','20110417','20110305','0','1' UNION ALL
SELECT '36','20091128','20101128','20100916','1','0' UNION ALL
SELECT '61','20090526','20100526','20090810','0','1' UNION ALL
SELECT '61','20090526','20100526','20090810','0','1' UNION ALL
SELECT '35','20070405','20071005','20070903','1','0' UNION ALL
SELECT '61','20090705','20100105','20090919','0','1' UNION ALL
SELECT '61','20090705','20100105','20090919','0','1' UNION ALL
SELECT '67','20080719','20090119','20081017','0','1' UNION ALL
SELECT '56','20090516','20100516','20090914','0','1' UNION ALL
SELECT '29','20100503','20101103','20100726','0','1'  

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.03 seconds. Powered By: Snitz Forums 2000