Stored Procedure HELP!!! - 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)

Stored Procedure HELP!!!

Author

Topic

tonydang2002
Starting Member

15 Posts

Posted - 2014-03-24 : 18:23:30

Hi, Is there a way to quality the stored procedure with something else rather than single '' or double quote "".

Is there a way to set that I can execute a SP as below:

for example:
EXEC sp_test $input string$ or EXEC sp_test {input string}

Thanks,
Tony

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2014-03-25 : 09:07:59

What is the problem? Are you trying to pass a string and need to past a quote?

djj

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-03-25 : 12:09:26

Try []:

EXEC sp_test [input string]

tonydang2002
Starting Member

15 Posts

Posted - 2014-03-25 : 12:36:35

Yes, I'm using the SP to load data into a table. however the string being passed in have both single quote and double quote. Therefore I can't use them and looking if there is a way to use something else like

EXEC sp_test [input string] but it doesn't work.

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-03-25 : 13:06:55

For a quote/dblquote within a string, you need to double the embedded quotes.

For example:

EXEC sp_test 'test''test''string''' = test'test'string'

tonydang2002
Starting Member

15 Posts

Posted - 2014-03-25 : 14:24:07

No, I mean my string is data and have something like these: 1|test's|~2|test"s where vertical bar and ~ are col delimited and row terminator.

for example:
EXEC sp_test '1|test1's|~2|test2"s'. This does not work since it think the string ends at the ' after test1. still looking for workaround on this.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-03-25 : 14:30:54

Do either of the options Scott suggested. In your example, do one of these:


-- string you want to send is 1|test's|~2|test"s
EXEC sp_test [1|test's|~2|test"s];
EXEC sp_test '1|test''s|~2|test"s;

Please note that when you use single quotes to define the string literal, you are escaping each embedded single quote with another single quote. There is nothing you need to do to escape the double-quote.

tonydang2002
Starting Member

15 Posts

Posted - 2014-03-25 : 16:38:02

I tried EXEC sp_test [1|test's|~2|test"s];, it doesn't work. Is there any option that I have to set first for the EXEC to work with [] or {}???

Thanks.

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-03-25 : 17:03:49

Try doubling all the single quotes instead.

In TSQL, I would use code below to replace all single quotes within the string with two single quotes instead:

DECLARE @string varchar(1000)
SET @string = REPLACE(@string, '''', '''''')

I'm not sure how to do it in whatever language you are using to call the proc.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources