Getting return value from EXEC SP_EXECUTESQL 1

[litton1]

Hi all, I am trying to build some dynamic sql. The first bit works but cannot get the second to work? Any help appreciated.

	declare @pWhereClause VARCHAR(50);
	set @pWhereClause = ' a.Visible = 1 AND a.Archived = 0 ' + @pWhereClause;
	declare @recCount INT;
	declare @sql NVARCHAR(500)

	EXEC SP_EXECUTESQL N' select @recCount = count(*) 
	FROM [PomPomplingAddresses] a 
		LEFT JOIN [PomWebProperties] wp ON wp.PomplingAddressID = a.PomplingAddressID
		LEFT JOIN PomRentalProperties prp ON prp.PomplingAddressID = wp.PomplingAddressID 
		LEFT JOIN [PomPropertyImages] ppi ON ppi.PomplingAddressID = a.PomplingAddressID AND ppi.IsMainImage = 1  
		', N'@recCount INT OUTPUT', @recCount OUTPUT

		select @recCount

adding new var @pWhereClause at the end gives error

declare @pWhereClause VARCHAR(50);
	set @pWhereClause = ' a.Visible = 1 AND a.Archived = 0 ' + @pWhereClause;
	declare @recCount INT;
	declare @sql NVARCHAR(500)

	EXEC SP_EXECUTESQL N' select @recCount = count(*) 
	FROM [PomPomplingAddresses] a 
		LEFT JOIN [PomWebProperties] wp ON wp.PomplingAddressID = a.PomplingAddressID
		LEFT JOIN PomRentalProperties prp ON prp.PomplingAddressID = wp.PomplingAddressID 
		LEFT JOIN [PomPropertyImages] ppi ON ppi.PomplingAddressID = a.PomplingAddressID AND ppi.IsMainImage = 1 
                       @pWhereClause', N'@recCount INT OUTPUT', @recCount OUTPUT
	
		select @recCount

How can I add the var @pWhereClause to the end of the sql?
Thx

Age is a consequence of experience

 

[softhemc]

I don't have SQL Server currently installed, but I think you need to use something like this:

[tt]ppi.IsMainImage = 1 @pWhereClause', N'[/tt]

needs to be changed to

[tt]ppi.IsMainImage = 1' + @pWhereClause + ',' + N'[/tt]


because @WhereClause is already a string

 

[litton1]

Thanks for your reply softhemc, I did already try that but I get - Incorrect syntax near '+'. Sql server doesn't seem to like a concatenated string when using EXEC SP_EXECUTESQL? But i do it this way so I can have an out param.

Age is a consequence of experience

 

[bborissov]

DECLARE @pWhereClause NVARCHAR(50);
-- You should have an AND in front of this because you already have some conditions in the JOIN
-- IF this is true WHERE clause you should change AND to WHERE, because you do not have WHERE clause
SET @pWhereClause = ' AND a.Visible = 1 AND a.Archived = 0'
DECLARE @recCount INT;
DECLARE @sql NVARCHAR(500)
-- Concatenate all in a variable, do not use direct concatenation in sp_executesql
SET @sql =
N'SELECT @recCount = count(*) 
         FROM [PomPomplingAddresses] a 
 LEFT JOIN [PomWebProperties] wp ON wp.PomplingAddressID = a.PomplingAddressID
 LEFT JOIN PomRentalProperties prp ON prp.PomplingAddressID = wp.PomplingAddressID 
 LEFT JOIN [PomPropertyImages] ppi ON ppi.PomplingAddressID = a.PomplingAddressID AND ppi.IsMainImage = 1'+@pWhereClause


EXEC SP_EXECUTESQL @sql, N'@recCount INT OUTPUT', @recCount = @recCount OUTPUT

SELECT @recCount

NOT TESTED!

Borislav Borissov
VFP9 SP2, SQL Server

 

[litton1]

Okay I dumb!
You correct something not quite right with that? I will take a look later and let you know..

You know when you mess with something so much you loose track of what you have changed and just end up breaking it more.. (Excuses are coming out)

Thanks for taking the time to look…


Age is a consequence of experience

 

[litton1]

Your example worked well, thanks have a star.

Age is a consequence of experience