Re: set a select statement stored in a table in a variable

From: Hari Prasad (hari_prasad_k_at_hotmail.com)
Date: 07/22/04 

Date: Thu, 22 Jul 2004 15:51:39 +0530

Hi,

You do not require a dynamic sql statement to use a variable after = clause.

You script should be some thing like

declare @patcode varchar(10)
set @patcode='hari123'
SELECT PATLAST FROM PAT1FILE WHERE PATCODE like @PATCODE

You need to have dynamic sql only if you construct the statment with
differnt table names or column names.

Thanks
Hari
MCDBA

"Korina" <karditsi@csd.uoc.gr> wrote in message
news:#bR0FR9bEHA.3012@tk2msftngp13.phx.gbl...
> Hello,
>
> I have a table containing select statements
> in each of it's columns.
>
> Inside a stored procedure I want to set
> the value of the column i.e the statement
> into a variable and then execute it using
> the command
> EXEC sp_executesql @QUERY
>
> If I print the value of the variable containing
> the query I get the statement
> 'SELECT PATLAST FROM PAT1FILE WHERE PATCODE = "' +@PATCODE+'"'
>
> but when i execute the store procedure
> I get the error
> Incorrect syntax near 'SELECT PATLAST FROM PAT1FILE WHERE PATCODE = "'.
>
> Does anynone have any idea?
>
> Thanks in advance
> Korina
>
>
> **********************************************************************
> Sent via Fuzzy Software @ http://www.fuzzysoftware.com/
> Comprehensive, categorised, searchable collection of links to ASP &
ASP.NET resources...

Relevant Pages

  • RE: SQL stored procedure executing twice
    ... I wasn't aware that DLookupwould execute the "domain" more than once. ... caused the stored procedure to execute twice. ... Dim stDocName As String ... My pass-thru query properties ...
    (microsoft.public.access.modulesdaovba)
  • Re: Database Engine Tuning Advisor suggestion to replace syntax.
    ... stored procedure, the user needs EXECUTE rights to the stored procedure. ... Query 1 requires the user to have SELECT rights on the Customers ... Also, FWIW, the dynamic SQL is only syntax checked at run time, not when the ...
    (microsoft.public.sqlserver.tools)
  • Re: Logging within User_Defined Functions (UDF)
    ... You can't perform dynamic SQL or DML in a function. ... Looks like a stored procedure that you ... Ultimately you can only execute ... > DECLARE @cmdstr nvarchar ...
    (microsoft.public.sqlserver.programming)
  • Re: Weird SqlDataReader: Invalid attempt to read when no data is present.
    ... When I execute the stored ... I know my stored procedure is working ok. ... > the SQL Query analyser and got results. ... The data reader object's HasRows ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: stored procedure security
    ... > execute privelages on the stored procedure, ... As soon as you employ dynamic sql, the logon executing the stored procedure ... In order to execute this stored ...
    (microsoft.public.sqlserver.security)
Quantcast