Need help with Stored Procedures - "Must declare the variable"

CREATE PROCEDURE [dbo].[test]
@id, int, @name varchar(12) OutPut, @setor int output, @ult_id int output, @ult_del int output, @tot int output
AS
declare @testing varchar(300)
SELECT @name = name, @setor = setor FROM users WHERE id = @id
select @testing = 'SELECT @ult_id = ult_id, @tot = tot, @ult_del = ult_del FROM Setor'+convert(varchar, @setor)+'ppl WHERE id_msg = 1'
Execute (@buscaChat)


but when i execute this it return the error "Must Declare the variable ult_id"
so how do i select the fields (ult_id, tot and ult_del) from the table wich is called SetorXppl (where x is a number from 1 to 64) like you see i tried putting it in a variable and then execute it, but it raises me that error.. so can anyone help me ?

thx in advance
LVL 7
furmigaAsked:
Who is Participating?
 
LowfatspreadCommented:
its not a good design to use dynamic sql ...


however this would work

CREATE PROCEDURE [dbo].[test]
@id, int, @name varchar(12) OutPut, @setor int output, @ult_id int output, @ult_del int output, @tot int output
AS
set nocount on
declare @testing varchar(300)
SELECT @name = name, @setor = setor FROM users WHERE id = @id
select 0 as ult_id,0 as Tot, 0 as ult_del
  into ##temp
 where 0=1
select @testing = 'insert into #temp select ult_id, tot, ult_del FROM Setor'+convert(varchar, @setor)+'ppl WHERE id_msg = 1'
Execute (@testing)
select @ult_id=ult_id,@tot=tot,@ult_del=ult_del
 from ##temp
drop table ##temp
return



a better way would probably be to have the setor tables defined in a view...
and just select from that ...
0
 
Atlanta_MikeCommented:
First thing, try to avoid using dynamic SQL as much as possible. By using Dynamic SQL it is less likely the execution plan will be properly cached.

The statement you are building will be in a separate transaction, so you have to declare it inside the string built. You won't be able to use the variable elsewhere in the query.

CREATE PROCEDURE [dbo].[test]
@id, int, @name varchar(12) OutPut, @setor int output, @ult_id int output, @ult_del int output, @tot int output
AS
declare @testing varchar(300)
SELECT @name = name, @setor = setor FROM users WHERE id = @id
select @testing = 'Declare @ult_id;SELECT @ult_id = ult_id, @tot = tot, @ult_del = ult_del FROM Setor'+convert(varchar, @setor)+'ppl WHERE id_msg = 1;print @ult_id'
Execute (@buscaChat)
0
 
furmigaAuthor Commented:
hey Lowfatspread your code worked, but it raises a error at
drop table ##temp (Invalid Object name: temp) if i remove that line it works but..

and btw.. since u both said its a bad ideia to use dynamic sql then.. in what way could i do to select tables that i do not know its name (like in my example up, where my table name could be from setor1ppl to setor64ppl)

u said to me use views.. but unfortunly im noob with sql server and stuff, so if you could show me some tutorials that i could learn i would appreciate..

anyway thx

(ps. sorry for my crap english but its not my main language)
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
furmigaAuthor Commented:
well after a basic reading on w3schools i came with this, but i dont see in what its diff..

CREATE VIEW [anything1] AS
SELECT ult_id, tot, ult_del
FROM Setor1ppl
WHERE id_msg = 1

i mean on the sp i would have to make dynamic sql aswell since that way i would have anything1 to 64....
well if anyone can help on making a better way i would appreciate alot..
thx
0
 
LowfatspreadCommented:
your basic problem is the design which has lead to there being 64 setor tables in the first place...

why is that  (i'm assuming they all have the same structure...)

if there is a physical reason for them to be separate then you should consider implementing them as a partitioned view...
available from sql2000 and above...

that way you can create a view which points to all of your individual tables and SQL server dynamically choose the
physical table it needs to access...

check BOL (Books on Line) the Help manuals that come with the SQL server client tools.


the other way (and better) assuming the same table structure would be to add a "Setname" to the table and include the setname/number as part of the key...

so that all data would reside on the one table but be separated into its logical groups...

hth

0
 
furmigaAuthor Commented:
let me see if i got this correct...

CREATE VIEW setors
AS
SELECT * FROM setor1ppl
UNION ALL
SELECT * FROM setor2ppl
UNION ALL
SELECT * FROM setor3ppl
UNION ALL
SELECT * FROM setor4ppl
UNION ALL
SELECT * FROM setor5ppl

and then
SELECT *FROM setors WHERE ? now.. what would i do for the where clause.. all my tables have the same structures, but besides their name there is nothing inside them that could tell wich table it is... so i cant follow the sql tutorial correctly..

and also.. i have many other UPDATE/INSERT/DELETE by the same way

Setor'+convert(varchar, @setor)+'ppl

so i guess it should be a good ideia to find a solution somehow... anyway if you could help me or perhaps point me a website that could help me i would appreciate... thx
0
 
Atlanta_MikeCommented:
CREATE VIEW setors
AS
SELECT 1 as setor,* FROM setor1ppl
UNION ALL
SELECT 2 as setor,* FROM setor2ppl
UNION ALL
SELECT 3 as setor,* FROM setor3ppl
UNION ALL
SELECT 4, as setor,* FROM setor4ppl
UNION ALL
SELECT 5  as setor,* FROM setor5ppl
0
 
LowfatspreadCommented:
the partitioned view approach basically relies on you having a restricted range of keys in a constraint
which will guide sql server in indentifying which physical table the data should reside in...

... i feel  that exploring your application/database design is probably best considered as another question


hth
   
0
 
Atlanta_MikeCommented:
Ahh, you're talking about horizontal partitioning... fun, fun.
0
 
furmigaAuthor Commented:
well... i redesigned it so it would not use dynamic sql in anyway.. but..
before i had 64 tables with around 100rows..
now i have 1 table with 6400rows... not using dynamic sql will make the search as fast (or faster) than b4? even if this new table has much more rows?
thx
0
 
furmigaAuthor Commented:
or is it better to maintan all tables but make 1 stored procedure for each ?
0
 
Atlanta_MikeCommented:
No one stored procedure should work fine.

You do want to checkout the information about horizonal partitioned views. You could benefit from this.


0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.