New to Stored Proceedure need assistance please

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

General SQL Server Forums

New to SQL Server Programming

New to Stored Proceedure need assistance please

Author

Topic

mrtweaver
Yak Posting Veteran

67 Posts

Posted - 2009-08-18 : 08:39:52

I am writing a stored proceedure and was wondering if this was at all possible and if so how one would go about doing it.

In my stored proceedure I have two main tables I am working with. One is called merge1 and the other is called merge2. After these two tables are generated I have a select statement that returns the results to the calling application.

What I would like to do is be able to send over a variable to the stored proceedure that would select which temp table to pull the data from. I do have the variable defined as follows: @tablename varchar(12).

In my select statement I have:

Select machine, shift, start, mech1, mech2
from ??????
where machine = 6

The ???? is the area I am having trouble with. If I place in there @MERGE1, I get the data from that table. Likewise if i place in there @MERGE2, I get the data from that table. But when I place in there @tablename I get the error that I must declare @tablename. I have used ( ), [ ], ' ', " ", and { } around this and so far no luck. So either I am doing something wrong or it is something that can not be done. Can someone please assist.

I am using MS SQL 2005. Like I said all I want to do is send over to the stored proceedure a variable and use this variable in my FROM clause. Hope this is clear. Thanks and have a great day.

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-08-18 : 08:49:59

Hi

Did u used like this...or


DECLARE  @tablename varchar(12)
DECLARE  @SQL		NVARCHAR(1000)
   
SET @tablename='Raj_table'

SET @SQL=	'SELECT MACHINE, SHIFT, START, MECH1, MECH2
FROM '+ @TABLENAME+'
WHERE MACHINE = 6'

PRINT @SQL

EXEC SP_EXECUTESQL @SQL

-------------------------
R...

mrtweaver
Yak Posting Veteran

67 Posts

Posted - 2009-08-18 : 09:00:08

After doing some searching courtesy of GOOGLE I found something similar to what you describe and on a hunch went with it and it seems to be working as expected. Thank you very much for a quick responce.

Now I have but one other question that GOOGLE did not seem to have an answer for.

In the table I have the column is set as INT. I use this column in my where clause. So lets look at the column SHIFT it is set as INT.

Now in my where clause I have WHERE SHIFT = @SHIFT. With this i can send over via the variable @SHIFT a 1, 2 or 3. But is there a wildcard that I can send over that would more or less not care what the value is in this column? Kind like the * symbol this way the where clause would return all shifts?

I did in my variables define @SHIFT INT and when I try to send over any thing except a number 0 - 9 I get the error converting to INT message. If I send over a zero I get no result, if I send over a 1 I get all 1st shift and likewise for 2 and 3 but I am hoping for some sort of wild card that I can send over.

Thanks again and have a great day.

quote:

Originally posted by rajdaksha

Hi

Did u used like this...or


DECLARE  @tablename varchar(12)
DECLARE  @SQL		NVARCHAR(1000)
   
SET @tablename='Raj_table'

SET @SQL=	'SELECT MACHINE, SHIFT, START, MECH1, MECH2
FROM '+ @TABLENAME+'
WHERE MACHINE = 6'

PRINT @SQL

EXEC SP_EXECUTESQL @SQL

-------------------------
R...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-08-18 : 09:02:57

Search for Array+SQL Server in google

Madhivanan

Failing to plan is Planning to fail

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-08-18 : 09:08:26

Hi

Am not sure what u expect..but try this..

'WHERE SHIFT =' CONVERT(VARCHAR(8),@SHIFT)

-------------------------
R...

mrtweaver
Yak Posting Veteran

67 Posts

Posted - 2009-08-18 : 09:15:10

Well what I want to be able to do is from my application send over the stored proceedure a value and have the stored procedure return data as requested.

If I send over a 1 to my stored procedure I get all data from 1st shift.

If I send over a 2 to my stored procedure I get all data from 2nd shift.

If I send over a 3 to my stored procedure I get all data from 3rd shift.

Now lets say I want all data from all three shifts. What character or number or whatever would I send over to the stored procedure so that the where clause:

WHERE SHIFT = @SHIFT

Would return all data for all 3 shifts. Hopefully this is more clear. If not please let me know. Thank you.

quote:
Originally posted by rajdaksha

Hi

Am not sure what u expect..but try this..

'WHERE SHIFT =' CONVERT(VARCHAR(8),@SHIFT)

-------------------------
R...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-08-18 : 09:18:09

Did you see my reply?

Madhivanan

Failing to plan is Planning to fail

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-08-18 : 09:21:21

Hi

You will send null

SET @SHIFT =NULL
'WHERE SHIFT ='+ISNULL(@SHIFT,SHIFT)

-------------------------
R...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources