November 26, 2011 at 9:48 am
please, can any body tell me how to declare and use variables, in a query any where u want..
please provide me with a simple syntax
November 26, 2011 at 10:08 am
I take it this is a home work question.
But here is a very simple example
CREATE PROCEDURE [dbo].[_AATT_Find_entry]
@LookFor VARCHAR(50)
AS
SELECT entryid,entry,RTRIM(description)+CHAR(10)+char(13),(snippet)
+CHAR(10)+char(13),[type],Version.sqlname+CHAR(10)+char(13)
FROM logentries
JOIN Version ON
logentries.sqlid = version.sqlid
WHERE entry LIKE('%'+@LookFor+'%') ORDER BY entryid
November 26, 2011 at 11:08 am
From Books On Line (The TSQL help file). It this this thing you are attempting?
sp_executesql supports setting of parameter values separately from the Transact-SQL string:
DECLARE @IntVariable INT;
DECLARE @SQLString NVARCHAR(500);
DECLARE @ParmDefinition NVARCHAR(500);
/* Build the SQL string one time. */
SET @SQLString =
N'SELECT * FROM AdventureWorks.Sales.Store WHERE SalesPersonID = @SalesID';
/* Specify the parameter format one time. */
SET @ParmDefinition = N'@SalesID int';
/* Execute the string with the first parameter value. */
SET @IntVariable = 275;
EXECUTE sp_executesql @SQLString, @ParmDefinition,
@SalesID = @IntVariable;
/* Execute the same string with the second parameter value. */
SET @IntVariable = 276;
EXECUTE sp_executesql @SQLString, @ParmDefinition,
@SalesID = @IntVariable;
November 28, 2011 at 3:43 am
madeeha.rehman5 (11/26/2011)
i m not asking in procedures.if i use this i dnt help me
declare @val varchar(200);
set @val=''; -- Write a valid SQL here.
exec (@Val)
but b/w this if i want to use the value of val any where in query error comes that u have not defined @val
If you just select 'exec (@Val)' and execute (F5) you will get above error.
Please select the complete code & execute, it should run without any issue.
Try Ron's code first. Then update your code accordingly.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply