Need to paste long SQL statement into variable

Question

Need to paste long SQL statement into variable

GBeezy

SSCarpal Tunnel

Points: 4723
More actions
July 7, 2011 at 2:26 pm

#243720

I am trying to paste a multiple line SQL statement into a variable and only the first line is making it in. A while back I was shown a website that could "smash" a SQL statement together for this purpose. I can not find that website via Google at all nor remember what it was.
Can some one please help me to be able to paste this SQL statement into a variable?
Thanks for the help

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

in SSMS, you just put everything between single quotes, lines and all: you might need to find and replace single quotes with two single quotes

DECLARE @sql varchar(max)

set @sql = '

CREATE VIEW VW_CITYCOUNTYSTATE

AS

SELECT TBCITY.CITYTBLKEY,

TBCITY.DESCRIP AS CITYNAME,

TBCOUNTY.COUNTYTBLKEY,

TBCOUNTY.DESCRIP AS COUNTYNAME,

TBCOUNTY.CODE AS COUNTYFIPS,

TBSTATE.STATETBLKEY,

TBSTATE.STATECODE,

TBSTATE.STATENAME

AS CITYDESCRIP

FROM TBCITY

LEFT JOIN TBCOUNTY ON TBCITY.COUNTYTBLKEY=TBCOUNTY.COUNTYTBLKEY

LEFT JOIN TBSTATE ON TBCOUNTY.STATETBLKEY=TBSTATE.STATETBLKEY

'

PRINT @sql

exec(@sql)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 2

Download BIDSHelper. It has a variable expression editor window that will allow you to paste multi-line SQL statements into a variable.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

GBeezy SSCarpal Tunnel Points: 4723 More actions · Answer 3

Thanks guys for the help...

A developer here showed me a trick in Notepad++. You can take your query paste into Notepad++, make sure it is highlighted, go to the Edit menu and select Join Lines.

Worked great for me.

Thanks Again for the suggestions!