insert in a stored procedure - 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 2005 Forums

Transact-SQL (2005)

insert in a stored procedure

Author

Topic

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2009-04-05 : 22:14:33

how come something like:

declare @sql varchar(4000),
		@xorderid varchar(1000) = 3670,
		@xusername varchar(1000) = 'albert@natimark.com'	
	
set @sql = 'insert into suppression select '''+@xorderid+''' as orderid,last_name,first_name,street,zip,phone,'''+@xusername+''' as username from order'+@xOrderID
print @sql
execute @sql

wont work? i'm trying to work this into a stored procedure, but even in just a normal query i get the same error:

insert into suppression select '3670' as orderid,last_name,first_name,street,zip,phone,'albert@natimark.com' as username from order3670
Msg 2812, Level 16, State 62, Line 7
Could not find stored procedure 'insert into suppression select '3670' as orderid,last_name,first_name,street,zip,phone,'albert@natimark.com' as username from order3670'.

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-04-05 : 23:58:07

The query doesn't make sense. You asking to return results from table ORDER3670.

I think you want the query to read


set @sql = 'insert into suppression select OrderID,last_name,first_name,street,zip,phone,'''+@xusername+''' as username from order where orderid = '+@xOrderID

execute @sql

The above query assumes there is a column called orderid

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

micnie_2020
Posting Yak Master

232 Posts

Posted - 2009-04-06 : 00:12:02

Dear Sir,

Here is just an idea on how your stored procedures suppose to be. (E.g.:-Northwind database)


Alter PROCEDURE ListOrder(  
--exec ListOrder 'listttt'
--drop table listttt
--select * from listttt
@usrTbl As VarChar(50)
) as    
 SET NOCOUNT ON    
declare @sSql nvarchar(4000)                                                            
declare @xorderid  varchar(20)                                                            
declare @xusername  varchar(20)                                                            
                                                     
set @xorderid= 3670     
set @xusername = 'albert@natimark.com'                                                            
                                                    
   
CREATE TABLE  #suppression 
(
	[ID] [int] IDENTITY (1, 1) NOT NULL ,    
    	orderid varchar(20) , 
	employeeid varchar(20),
	customerid varchar(20),
	orderdate datetime,
	email varchar(100)
)  

set @sSql=N'insert into #suppression (orderid,employeeid,customerid,orderdate,email)  '
set @sSql=@sSql + 'select '''+@xorderid+''' as orderid,employeeid,customerid,orderdate,'
set @sSql=@sSql + ' ''albert@natimark.com'' as username from orders '



-- print @sSql 
EXEC SP_EXECUTESQL @sSql 

Set @sSql = 'SELECT * INTO '+ @usrTbl +' FROM #suppression '                                    
EXEC SP_EXECUTESQL @sSql 
 
set nocount off

p/s: If you are about to use { ... from order'+@xOrderID; } make sure that your database having tablename called order3670.

Regards,
Michelle

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2009-04-06 : 01:16:32

hum...

so looks like both of you mis-understood me. sorry,

basically i'm trying to take: the two variables (the orderid and the username)
and populate them into data that i'm going to insert into a table of everything the client has gotten, (the suppression table)
the variables are passed from a vb.net site i wrote.

so i wanna make the below insert statement work from:


set @sql=N'insert into suppression (orderid,last_name,first_name,street,zip,phone,username)  '
set @sql=@sql+'select '''+@xorderid+''' as orderid,last_name,first_name,street,zip,phone,'
set @sql=@sql+''''+@xusername+''' as username from order'+@xOrderID
execute @sql

which creates:

insert into suppression (orderid,last_name,first_name,street,zip,phone,username)  select '3670' as orderid,last_name,first_name,street,zip,phone,'albert@natimark.com' as username from order3670

but i still get:

Msg 203, Level 16, State 2, Line 493
The name 'insert into suppression (orderid,last_name,first_name,street,zip,phone,username)  select '3670' as orderid,last_name,first_name,street,zip,phone,'albert@natimark.com' as username from order3670' is not a valid identifier.

any ideas? what am i missing?

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-04-06 : 01:26:57

set @sql=N'insert into suppression (orderid,last_name,first_name,street,zip,phone,username) '
set @sql=@sql+'select '''+@xorderid+''' as orderid,last_name,first_name,street,zip,phone,'
set @sql=@sql+''''+@xusername+''' as username from order where orderid ='+@xOrderID
execute @sql

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2009-04-06 : 01:36:08

nooo...

i'm trying to get all the records in order1234 (but the 1234 part is dynamic, and it matches the xorderid variable)
so it can be order1234, order3118, or as the example @ the top, 3670.

follow?

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2009-04-06 : 02:30:01

also this doesnt work, same error, just thought it'd be worth mentioning... i'm sure i'm just being dumb on making the procedure...


alter procedure [dbo].[dedupe]
(@tblname varchar(20))
As
declare @sql varchar(1000),
		@temptable varchar(20)

SET NOCOUNT ON
set @temptable=@tblname
set @sql='
	DELETE T1 FROM ' + @temptable + ' AS T1	JOIN (		SELECT [HH_ID#],[MAX_MYRECORDID]=MAX(UID) FROM ' + @temptable + ' GROUP BY [HH_ID#]) AS T2
			ON T2.[HH_ID#]=T1.[HH_ID#] AND T2.MAX_MYRECORDID<>T1.UID
'
exec @sql

used:

exec dedupe 'testdupe'

to execute, testdupe being the table to test against

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-04-06 : 03:43:57

Pleasce confirm...

Do you have a table in your database called "Order3118" Not a record, or a column, a actual table?

Your query is trying to pull records from a table labled "Order3118"

I assumed what you were asking is to pull records that had a orderID of 3118, and that was the example I illustrated as did BKLR.

Please provide some sample data and Table structure and desired results, so we can make more sense of this. I'm still having trouble following you exactly.

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

micnie_2020
Posting Yak Master

232 Posts

Posted - 2009-04-06 : 03:54:40

Dear sir,

Please replace your execute @sql to EXEC SP_EXECUTESQL @Sql. Then it's solved error "is not a valid identifier".

Thank you.

Regards,
Michelle

set @sql=N'insert into suppression (orderid,last_name,first_name,street,zip,phone,username) '
set @sql=@sql+'select '''+@xorderid+''' as orderid,last_name,first_name,street,zip,phone,'
set @sql=@sql+''''+@xusername+''' as username from order'+@xOrderID
~~execute @sql~~

EXEC SP_EXECUTESQL @Sql

:
:
:
Msg 203, Level 16, State 2, Line 493
The name 'insert into suppression (orderid,last_name,first_name,street,zip,phone,username) select '3670' as orderid,last_name,first_name,street,zip,phone,'albert@natimark.com' as username from order3670' is not a valid identifier.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-04-06 : 04:42:20

Or even just



EXEC (@sql)

*with* the brackets

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2009-04-06 : 11:31:02

thanks charlie, i figured it out late last night... (using the exec (@sql) method)
figured it out from some old code i had.

Thanks!

asgast
Posting Yak Master

149 Posts

Posted - 2009-04-06 : 11:38:38

you should use EXEC sp_executesql @sql as micnie_2020 has shown
there are many reasons for that

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2009-04-06 : 11:43:35

every time i've tried it i get all kinda a crapola about parameters and what not.

what's the difference? can you point me to something?

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-04-07 : 05:54:02

The biggest difference is that


EXEC sp_executeSql

Can reuse query plans which is great if you've written some dynamic sql inside a stored proc. Also you get to pass variables in and out and generally write some more readable code.

There used to be a problem with max number of characters (As the dynamic block has to be a NVARCHAR string) but form 2005 onward (assuming your database is in compat level 90) that's been removed and you can use the NVARCHAR(MAX) datatype.

Check out this site:
http://www.sommarskog.se/dynamic_sql.html

For a great resource on all things dynamic SQL.

all the best,
Charlie.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2009-04-07 : 11:54:53

thanks alot, i'll book mark that bad-boy and check it out. i wrote a dynamic sql procedure a while back... just copied and pasted alot of it though because i'm a totally nub with this... no formal training, and the bosses are to cheap to get me some, so just kinda learning the ins/outs as i go. thanks alot!

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-04-07 : 12:22:59

That link taught me so much about SQL. It's a fantastic page.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2009-04-07 : 12:30:35

yeah, oddly enough, this is where i built that script from forever ago, because of that site, i was able to reduce my workload by like 1/2

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2009-04-07 : 23:48:52

okay, so new, but relevant question....

how can i get the follow stored procedure to work with temporary tables, or a table only visible to that session.

when i pull data i need to dedupe, i always drop it into #work. then dedupe it, then select * from #work. i'm trying to do that but with stored procedures.

USE [tempdb]
create procedure [dbo].[dedupe_phone]
(@temptable varchar(20))
As
SET NOCOUNT ON
DECLARE @sql2 varchar(8000)
 SELECT @sql2 = '
 if not exists(SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='''+@temptable+''' and COLUMN_NAME=''UID'')
	alter table '+@temptable+' add uid int NOT NULL IDENTITY (1, 1)'
 exec(@sql2)

 select @sql2 = ' DELETE T1 FROM ' + @temptable + ' AS T1 JOIN (SELECT phone,[MAX_MYRECORDID]=MAX(UID) 
   FROM ' + @temptable + ' GROUP BY phone) AS T2
	ON T2.phone=T1.phone AND T2.MAX_MYRECORDID<>T1.UID'
 exec(@sql2)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources