Stored Procedure with SQL Insert Statement

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

Transact-SQL (2000)

Stored Procedure with SQL Insert Statement

Author

Topic

MarkusS
Starting Member

5 Posts

Posted - 2006-07-10 : 03:42:00

Hello Forum !
Ive got the following problem:

i want to create a Stored Procedure which insertes Data into a table, but i want to create the insert String in my sourcecode and give the String as a parameter:

Table: Test
TestIdx int ( primary key )
test varchar (50)

Stored Procedure:

CREATE PROCEDURE sp_test @SQL varchar(800) as
-- Excecute the SQL String
exec @SQL

-- Do some work
select max(testidx) from test

Execute:

exec test 'INSERT INTO [DLS4000].[dbo].[test]([test]) VALUES('juhu2')'

How can i replace the apostroph ' ??

How can i modify the sp, that it finally works ?

Greetings frm Germany,
Markus

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-07-10 : 03:50:25

Somthing like this
exec test 'INSERT INTO [DLS4000].[dbo].[test]([test]) VALUES('''juhu2''')'

Chirag

MarkusS
Starting Member

5 Posts

Posted - 2006-07-10 : 03:57:06

'''juhu2'''
Sorry - doesn't work !!
"' and '" doesnt work,too

Thanks for superfast reply !

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-10 : 04:00:02

to specify the single quote in a string, escape it by specifying it twice

exec test 'INSERT INTO [DLS4000].[dbo].[test]([test]) VALUES(''juhu2'')'

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-07-10 : 04:07:37

opps sorry

Check out this ..


-- Your procedure should be like this .. 
CREATE PROCEDURE sp_test @SQL varchar(800) 
as
Begin 
-- Excecute the SQL String
exec (@SQL)

-- Do some work
select max(testidx) from test

End 

-- Calling your procedure .. 

exec sp_test "INSERT INTO [DLS4000].[dbo].[test]([test]) VALUES('juhu2')"

--- or you can use the Tan Methods it should also work for you..

Chirag

MarkusS
Starting Member

5 Posts

Posted - 2006-07-10 : 04:12:38

I LOVE THE FORUM !! SUPERTHANKS

Solution:

CREATE PROCEDURE sp_test @SQL nvarchar(800) as
EXECUTE sp_executesql @SQL
select max(testidx) from test

exec ats_test 'INSERT INTO [DLS4000].[dbo].[test]([test]) VALUES(''IT WORKS'')'

Solved Probs:
Execute works with sp_executesql
Apostroph has to be inserted twice !

THANKS !!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-10 : 04:14:49

Why are you using Dynamic SQL ? Is there anything that you want to achieve that is only possible with Dynamic SQL ?

Refer to here http://www.sommarskog.se/dynamic_sql.html

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-07-10 : 04:59:29

Dont pass sql statements as parameters. Read the link posted by Tan fully

Madhivanan

Failing to plan is Planning to fail

MarkusS
Starting Member

5 Posts

Posted - 2006-07-14 : 08:50:19

YES, for my Program it is right.

I want to create a stored procedure with this insert statement, because in this statement are many different parameters.
With the new table entry i have to do many things in the database, insert other stuff in other in other tables and so on.

This stored procedure is called by my program in a thread, so that all users could work on without any blockade.

BUT . .

Other Programs are also inserting Table entrys in this table , there are no other calulations necessary. ( -> NO TRIGGER possible )

Changes in the database design are not possible.

Greetings from Germany !
Markus

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources