declaring table variable - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

declaring table variable

Author

Topic

deverdits
Starting Member

6 Posts

Posted - 2011-09-26 : 01:01:55

Hi everyone,

I'm new to sql and running into a problem declaring a table variable, not sure what I'm doing wrong...

On the Insert event of a form I'm using the following statement

declare @tt1 table (id int)
insert into law (lawname, lawdescription, lawcountry,lawcode,lawstartdate, lawenddate, lawrev, lawrefurl)
output inserted.lawid into @tt1
values (@lawname, @lawdescription,@lawcountry,@lawcode,@lawstartdate,@lawenddate, @lawrev, @lawrefurl)

but when I submit the page I get this error

The variable name '@tt1' has already been declared. Variable names must be unique within a query batch or stored procedure.
Must declare the table variable "@tt1".

What am I missing? Not sure why it tells me first that it's already declared but then it tells me I have to declare it.

Any help appreciated.

Thanks
John.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-26 : 01:24:17

is this your full batch of query running? if not post the full query

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-09-26 : 06:11:29

If the code you posted is inside a loop or something you must move the declaration outside the loop. A local variable can only be declared once inside the current scope.

- Lumbago
My blog-> http://thefirstsql.com/2011/07/08/how-to-find-gaps-in-identity-columns-at-the-speed-of-light/

deverdits
Starting Member

6 Posts

Posted - 2011-09-26 : 07:57:09

That's all that's included in the form datasource's Insert operation. Normally I'd only have the insert statement, but this time around I'm trying to get the inserted record's ID back so I added in the Output option, but need to store the value somewhere so I'm trying to define a table for it.

This code below is from the webpage showing all the code related to the datasource commands, but what I posted initially is all I have in the Configure Datasource Insert action.

Thanks.

<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:STRKnowledgeDBConnectionString %>"

SelectCommand="select lawid, lawname, lawdescription, lawcountry, countryname, lawcode, lawcomment, lawstartdate, lawenddate, lawattachment, lawrev,lawrefurl from law, loc_country where loc_country.countryid = law.lawcountry and lawid=@lawid"

InsertCommand="declare @tt1 table (id int)
insert into law (lawname, lawdescription, lawcountry,lawcode,lawstartdate, lawenddate, lawrev, lawrefurl)
output inserted.lawid into @tt1
values (@lawname, @lawdescription,@lawcountry,@lawcode,@lawstartdate,@lawenddate, @lawrev, @lawrefurl)"

UpdateCommand="update law set lawname=@lawname, lawdescription=@lawdescription, lawcountry=@lawcountry, lawcode=@lawcode, lawcomment=@lawcomment, lawstartdate=@lawstartdate, lawenddate=@lawenddate, lawrev = @lawrev, lawrefurl=@lawrefurl where lawid=@lawid">
<SelectParameters>
<asp:QueryStringParameter Name="LawID" QueryStringField="LawID" />
</SelectParameters>
<UpdateParameters>
<asp:Parameter Name="lawname" />
<asp:Parameter Name="lawdescription" />
<asp:Parameter Name="lawcountry" />
<asp:Parameter Name="lawcode" />
<asp:Parameter Name="lawcomment" />
<asp:Parameter Name="lawstartdate" />
<asp:Parameter Name="lawenddate" />
<asp:Parameter Name="lawrev" />
<asp:Parameter Name="lawrefurl" />
<asp:Parameter Name="lawid" />
</UpdateParameters>
<InsertParameters>
<asp:Parameter Name="tt1" />
<asp:Parameter Name="lawname" />
<asp:Parameter Name="lawdescription" />
<asp:Parameter Name="lawcountry" />
<asp:Parameter Name="lawcode" />
<asp:Parameter Name="lawstartdate" />
<asp:Parameter Name="lawenddate" />
<asp:Parameter Name="lawrev" />
<asp:Parameter Name="lawrefurl" />
</InsertParameters>
</asp:SqlDataSource>

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-26 : 08:36:06

i think you're trying to do multiple insert action which case it tryingto create table variable multiple times and hence the error. why not put the code inside a procedure and pass it once for set of values?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

deverdits
Starting Member

6 Posts

Posted - 2011-09-26 : 08:55:34

I'm only inserting one record. When I take out all the additional code, and just leave the plain insert statement, the info I have on the web page gets inserted into the my db table correctly, without anything additional, so I'm guessing only one insert is happening.

Not sure where or how I'd have to do the procedure.

[/quote]

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-09-26 : 09:27:50

Do like this instead:

declare @id int
insert into law (lawname, lawdescription, lawcountry,lawcode,lawstartdate, lawenddate, lawrev, lawrefurl) 
values (@lawname, @lawdescription,@lawcountry,@lawcode,@lawstartdate,@lawenddate, @lawrev, @lawrefurl)

set @id = scope_identity()[/code]

- Lumbago
My blog-> http://thefirstsql.com/2011/07/08/how-to-find-gaps-in-identity-columns-at-the-speed-of-light/

deverdits
Starting Member

6 Posts

Posted - 2011-09-26 : 09:44:50

That did the trick, except the first time I ran it I got the same error, but then I took out the Declare statement and it worked going forward. I think when I use the @ID in the statement, the parameter is automatically defined in the aspx code and that's enough.

Because of your set statement I don't need to use the Output option so this seems to be a better way than what I wanted to do initially. I'm using the Output option in another form but I already have an Audit table set up in the db so it's just putting things in there, I didn't need to define anything on the fly.

Thank you all for your help.

Kristen
Test

22859 Posts

Posted - 2011-09-26 : 10:47:35

" I don't need to use the Output option so this seems to be a better way than what I wanted to do initially"

Perhaps add a check, and fatal error, if more than one row is inserted (as only one row is going to be returned by scope_identity())

deverdits
Starting Member

6 Posts

Posted - 2011-09-26 : 11:15:59

Guess it's not a bad idea, even though I don't see how more than one record could get inserted with this one form.

Thanks.

Kristen
Test

22859 Posts

Posted - 2011-09-26 : 11:45:46

I don't know DotNet, so I don't know how difficult it would be to adapt the form to allow multiple records to be created, but my "defensive programming" style would want to guard against that with an alert / fatal error, just so that if & when the form is altered in the future it doesn't cause an obscure bug by failing to adjust the related table's records - i.e. it would only adjust one of them, and as such might work 99% of the time ...

Might be complete overkill for your scenario though.

deverdits
Starting Member

6 Posts

Posted - 2011-09-26 : 14:12:57

It probably is because the form is set up to accept info for only one record and to make it take more it would have to be completely redone including the sql part. But thanks for the suggestion.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources