Concatenation - 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

Concatenation

Author

Topic

kgundava
Yak Posting Veteran

66 Posts

Posted - 2011-08-02 : 10:59:58

Hi All,
I have to generate a where clause dynamically using a cursor.It would be helpful for me if you could let me know how can i concatenate a Varchar variable i.e @WHERECLAUSE with a string generated.
Please see below...
OPEN @WHERCLAUSE_CUR
FETCH NEXT FROM @WHERCLAUSE_CUR INTO @COLUMN_NAM
WHILE @@FETCH_STATUS = 0
BEGIN
@WHERECLAUSE + 'S.'+@COLUMN_NAM+'=T.'+@COLUMN_NAM+''
FETCH NEXT
FROM @WHERCLAUSE_CUR INTO @COLUMN_NAM
END

Thanks......

michael.appleton
Posting Yak Master

160 Posts

Posted - 2011-08-02 : 11:30:09

Cursors and dynamic SQL! Are you trying to wind to some people up here? Wait unitl Celko sees this...Hehe!

Seriously, this is probably not a good approach. You've asked how to concatenate a varchar variable, but you've done it in your example using the "+" operator sort of... If you want to save the results into the variable @WHERECLAUSE use:



SET @WHERECLAUSE=@WHERECLAUSE + 'S.'+@COLUMN_NAM+'=T.'+@COLUMN_NAM+' AND ' --Guessing you'll need to link up the different conditions somehow

But yeah, like I said, dynamic sql and cursors, usually a bad idea!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-02 : 11:48:56

why is your column varying? cant you determine columns before execution time?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2011-08-02 : 12:09:25

What visakh said -- this is probably a bad, bad plan.

However, here is some magic.

SET NOCOUNT ON
DECLARE @testTable TABLE ([val] VARCHAR(10))

INSERT @testTable ([val])
      SELECT 'a'
UNION SELECT 'b'
UNION SELECT 'c'
UNION SELECT 'd'

-- Start magic here
DECLARE @abracadabra VARCHAR(MAX) SET @abracadabra = ''

SELECT @abracadabra = @abracadabra + [val] + CHAR(13) + CHAR(10)
FROM @testTable
ORDER BY [val] ASC

-- End Magic

PRINT @abracadabra

Results:



a
b
c
d

You can concatenate strings just using SELECT - which will outperform a cursor by several hundred times. However, you probably shouldn't be doing what you are suggesting to do.

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

kgundava
Yak Posting Veteran

66 Posts

Posted - 2011-08-02 : 13:55:31

Thanks for the responses.I would be trying your approaches and let you guys know the result.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources