Placing Quotes '' in a query - 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

General SQL Server Forums

New to SQL Server Programming

Placing Quotes '' in a query

Author

Topic

Bill_C
Constraint Violating Yak Guru

299 Posts

Posted - 2009-03-24 : 09:06:34

I want to pass the following as a string into a procedure

select * from openquery(New_Srv,'Select * from Pol.Porting')

I have tried the following (which doesn't work)


DECLARE @str1 NVARCHAR(100),@str2 NVARCHAR(100),@str3 NVARCHAR(10),@str4 NVARCHAR(210)

SET @str1 = 'select * from openquery(New_Srv,'
SET @str2 = 'Select * from Pol.Porting'
SET @str3 = ')'
SET @str4 = @str1 + @str2 + @str3

EXEC @str4

But I fail on getting the quotes around the inner part of the query?
Any help please?

edit - done now thanks, forgot about doubling the quotes!

edit2 - nope, that didn't work either

heavymind
Posting Yak Master

115 Posts

Posted - 2009-03-24 : 09:22:14

looks like you need to duplicate quote 4 times..

Thanks, Vadym
MCITP DBA 2005/2008
Chief DBA at http://www.db-staff.com

Bill_C
Constraint Violating Yak Guru

299 Posts

Posted - 2009-03-24 : 09:37:39

What I want to achieve is:
select * from openquery(New_Srv,'Select * from Pol.Porting')

I just want to pass the part within quotes into the sp, so it needs to go in with '' around it.

I have tried doubling, trebling and quadrupling the single quotes to no avail!

heavymind
Posting Yak Master

115 Posts

Posted - 2009-03-24 : 09:49:12

SET @str1 = 'select * from openquery(New_Srv,'
SET @str2 = '''''Select * from Pol.Porting'''''
SET @str3 = ')'
SET @str4 = @str1 + @str2 + @str3

EXEC @str4
does not this work?

Thanks, Vadym
MCITP DBA 2005/2008
Chief DBA at http://www.db-staff.com

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-03-24 : 09:50:27

DECLARE @str1 NVARCHAR(100),@str2 NVARCHAR(100),@str3 NVARCHAR(10),@str4 NVARCHAR(210)

SET @str1 = 'select * from openquery(New_Srv,'
SET @str2 = '''Select * from Pol.Porting'''
SET @str3 = ')'
SET @str4 = @str1 + @str2 + @str3

SELECT @str4

gives me

select * from openquery(New_Srv,'Select * from Pol.Porting')

Good enough?

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-03-24 : 09:51:36

heavymind beat me to it....anyways...it solves ur problem.

Bill_C
Constraint Violating Yak Guru

299 Posts

Posted - 2009-03-24 : 10:14:28

Sorry both, I'm trying to do this all wrong! I need to pass just the inner string

Here's the code:


--------------create sp -----------------------------
IF OBJECT_ID ( 'usp_PLibrary', 'P' ) IS NOT NULL 
    DROP PROCEDURE usp_PLibrary;
GO
CREATE PROCEDURE usp_PLibrary
@PathFileName varchar(1000),
@PName2 varchar(1000)

AS
DECLARE @SQL NVARCHAR(1000)
SET @SQL = 'select * from openquery(New_Srv,' + @PathFileName + @PName2
print @SQL
exec @SQL
-----------------------------------------------------

------code to pass in --------------
DECLARE @SQL NVARCHAR(2000),@SQL2 NVARCHAR(2000),@SQL3 NVARCHAR(2000)
SET @SQL = '''Select * from Pol.Porting'''
SET @SQL3 = ')'
exec usp_PLibrary @SQL, @SQL3

What I get back is :-

select * from openquery(New_Srv,'Select * from Pol.Porting')
Msg 2812, Level 16, State 62, Procedure usp_PLibrary, Line 9
Could not find stored procedure 'select * from openquery(New_Srv,'Select * from Pol.Porting')'.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-03-25 : 03:46:17

http://sqlblogcasts.com/blogs/madhivanan/archive/2008/02/19/understanding-single-quotes.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources