Menu
I have the following dynamic query which is working fine without the WHERE clause, which is expecting UNIQUEIDENTIFIER.
When I pass it in, I don't get a result. I tried CAST and CONVERT, but no result. I might be doing it wrong, can anybody help?
CREATE PROCEDURE [dbo].[sp_Test1] /* 'b0da56dc-fc73-4c0e-85f7-541e3e8f249d' */
(
@p_CreatedBy UNIQUEIDENTIFIER
)
AS
DECLARE @sql NVARCHAR(4000)
SET @sql ='
DECLARE @p_CreatedBY UNIQUEIDENTIFIER
SELECT
DateTime,
Subject,
CreatedBy
FROM
(
SELECT
DateTime, Subject, CreatedBy,
ROW_NUMBER() OVER(ORDER BY DateTime ) AS Indexing
FROM
ComposeMail
WHERE
CreatedBy = @p_CreatedBy /* <--- the problem is in this condition */
) AS NewDataTable
'
EXEC sp_executesql @sql
388
Executing dynamic SQL queries Dynamic SQL queries are those built at runtime based on one or more variable values. To execute those queries, we must concatenate them into one SQL statement and pass them as a parameter to the sp_executesql stored procedure.
In the statement string of a dynamic SQL statement, a parameter marker represents a value that will be provided by the application program. The value of a parameter marker is provided on the EXECUTE or OPEN statement that is associated with the dynamic SQL statement.
Using CTEs, for instance, you can use SELECT from <subquery> in Open SQL. In my case I needed to execute dynamic SELECT count( DISTINCT col1, col2, …) which is not possible in the regular OpenSQL.
You must pass in the parameters to sp_executesql. See MSDN for details.
...
WHERE
CreatedBy = @p
...
EXECUTE sp_executesql @sql, N'@p UNIQUEIDENTIFIER', @p = @p_CreatedBY
Multiple parameter syntax. Maybe this will save someone an extra Google Search:
exec sp_executesql
@qry,
N'@value1 int, @value2 int, @currentValue int',
@value1 = @value1, @value2 = @value2, @currentValue = @currentValue
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
