sp_executesql

Question

sp_executesql

Stef Teal

SSCrazy

Points: 2382
More actions
July 25, 2008 at 10:00 am

#381941

I am not seeing the syntax error, help anyone?
DECLARE @sourceTable nvarchar(50)
DECLARE @sourceFieldId nvarchar(50)
DECLARE @sourceDisplayField nvarchar(50)
DECLARE @OutString nvarchar(500)
DECLARE @strSql nvarchar(500)
DECLARE @params nvarchar(20)
SET @strSql =
N'SELECT @Concat = ISNULL(@Concat + '', '', '''') + ISNULL(a.' + @sourceDisplayField + ', '''')' + CHAR(10)
+ 'FROM dbo.DataTable dt' + CHAR(10)
+ 'JOIN ' + @sourceTable + ' a ON dt.FieldValueId = a.' + CAST(@sourceFieldId AS varchar)+ CHAR(10)
+ 'WHERE dt.ddId = ' + CAST(@DocDataId AS varchar)
+ ' AND dt.dfOrgId = ' + CAST(@DocumentFieldOrgId AS varchar)
SET @params = N'@Concat nvarchar(500) OUTPUT'
EXECUTE sp_executesql @strSql, @params, @Concat = @OutString OUTPUT
--This is the resulting string--
SELECT @Concat = ISNULL(@Concat + ', ', '') + ISNULL(a.ServiceArea, '')
FROM dbo.DataTable dt
JOIN dbo.serviceArea a ON dt.FieldValueId = a.ServiceAreaId
WHERE dt.ddId = 1 AND dt.dfOrgId = 151

Viewing 10 posts - 1 through 9 (of 9 total)

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

Have you tried just running the resulting command?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 2

what error are you getting?

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

Stef Teal SSCrazy Points: 2382 More actions · Answer 3

Stef Teal

SSCrazy

Points: 2382

July 25, 2008 at 10:09 am

#848549

Incorrect syntax near the keyword 'SELECT'.

Stef Teal SSCrazy Points: 2382 More actions · Answer 4

Stef Teal

SSCrazy

Points: 2382

July 25, 2008 at 10:10 am

#848550

yes, the output runs fine directly.

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 5

OK I don't get the same error, but here is what I think.

If any of your variables that help build your string are null then it will all fall over.

I would recommened using isullout side of the acutally string incase of this situation.

I'll keep looking to see if there is anything else.

Could you also give us some examples of your variable values?

Thanks

Chris

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 6

Sorry , my bad, I have all the data I need 🙂

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 7

Wow that was driving me crazy.

the variable:

@params is too short so it's causing everthing to fall over.

Extend the length and all should be sorted 🙂

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

Stef Teal SSCrazy Points: 2382 More actions · Answer 8

Stef Teal

SSCrazy

Points: 2382

July 25, 2008 at 10:42 am

#848579

Stobbs for president! Thanks 🙂

Venkatesan Prabu SSCommitted Points: 1892 More actions · Answer 9

Just try this query,

DECLARE @sourceTable nvarchar(50)

DECLARE @sourceFieldId nvarchar(50)

DECLARE @sourceDisplayField nvarchar(50)

DECLARE @OutString nvarchar(500)

DECLARE @strSql nvarchar(500)

DECLARE @params nvarchar(20)

DECLARE @DocDataId varchar(20)

DECLARE @Concat varchar(200)

SET @strSql =

N'SELECT @Concat = ISNULL(@Concat + '', '', '''') + ISNULL(a.' + @sourceDisplayField + ', '''')' + CHAR(10)

+ 'FROM dbo.DataTable dt' + CHAR(10)

+ 'JOIN ' + @sourceTable + ' a ON dt.FieldValueId = a.' + CAST(@sourceFieldId AS varchar)+ CHAR(10)

+ 'WHERE dt.ddId = ' + CAST(@DocDataId AS varchar)

+ ' AND dt.dfOrgId = ' + CAST(@DocumentFieldOrgId AS varchar)

SET @params = N'@Concat nvarchar(500) OUTPUT'

EXECUTE sp_executesql @strSql, @params, @Concat = @OutString OUTPUT

--This is the resulting string--

SELECT @Concat = ISNULL(@Concat + ', ', '') + ISNULL(a.ServiceArea, '')

FROM dbo.DataTable dt

JOIN dbo.serviceArea a ON dt.FieldValueId = a.ServiceAreaId

WHERE dt.ddId = 1 AND dt.dfOrgId = 151

Thanks and Regards,
Venkatesan Prabu, 😛
My Blog:

http://venkattechnicalblog.blogspot.com/