how to construct dynamic sql in stored procedure

Hi Guys.

I want to construct a SQL in stored procedure in the following way. Please guide me how to achive this

CREATE PROCEDURE P_SAMPLE

(

@.P_ONE NVARCHAR(240)

)

AS

BEGIN

DECLARE

@.TMP_CNT INT

BEGIN

EXEC ('SELECT @.TMP_CNT = 1')

-- PRINT @.TMP_CNT

END

END

Regards

Mani

Use sp_executesql to pass/retrive the data to/from dynamic sql. Exec won’t help you here.

Code Snippet

DECLARE @.TMP_CNT INT

DECLARE @.SQL as NVarchar(4000)

DECLARE @.Params as NVarchar(4000)

Set @.SQL = N'SELECT @.TMP_CNT = 1'

Set @.Params = N'@.TMP_CNT as Int OUTPUT'

Exec sp_executesql @.SQL, @.Params, @.TMP_CNT OUTPUT

Select @.TMP_CNT

|||

here is one of the best resource in Dynamic sql

http://www.sommarskog.se/dynamic_sql.html

Madhu