Declare Variable for a Query String

Declare Variable for a Query String

It's possible, but it requires using dynamic SQL.

I recommend reading The curse and blessings of dynamic SQL before continuing...

DECLARE @theDate varchar(60)
SET @theDate = '''2010-01-01'' AND ''2010-08-31 23:59:59'''

DECLARE @SQL VARCHAR(MAX)  
SET @SQL = 'SELECT AdministratorCode, 
                   SUM(Total) as theTotal, 
                   SUM(WOD.Quantity) as theQty, 
                   AVG(Total) as avgTotal, 
                  (SELECT SUM(tblWOD.Amount)
                     FROM tblWOD
                     JOIN tblWO on tblWOD.OrderID = tblWO.ID
                    WHERE tblWO.Approved = ''1''
                      AND tblWO.AdministratorCode = tblWO.AdministratorCode
                      AND tblWO.OrderDate BETWEEN '+ @theDate +')'

EXEC(@SQL)

Dynamic SQL is just a SQL statement, composed as a string before being executed. So the usual string concatenation occurs. Dynamic SQL is required whenever you want to do something in SQL syntax that isn't allowed, like:

• a single parameter to represent comma separated list of values for an IN clause
• a variable to represent both value and SQL syntax (IE: the example you provided)

EXEC sp_executesql allows you to use bind/preparedstatement parameters so you don't have to concern yourself with escaping single quotes/etc for SQL injection attacks.

variable for query string

You need to "break" the string. The value @i is not available in the scope the string is executed in, so you need to make it part of the string.
Like so:

DECLARE @tmp NVARCHAR(10)
SET @tmp = 'POL'

DECLARE @sql VARCHAR(MAX)
SET @sql = 'SELECT

a.something1,
b.something2,
c.something3

FROM
aaa a WITH(NOLOCK)
LEFT JOIN bbb b WITH(NOLOCK) ON....
LEFT JOIN ccc c WITH(NOLOCK) ON....

WHERE
a.somethingelse = ''aa''
AND
((a.entirelysomethingelse=''aaa'') OR (a.entirelysomethingelse=''aaaa''))
AND
b.anotherdifferentsomething != 41
AND 
c.yetanotherdifferentthing LIKE(''%@%'')
AND
c.datafromvariablewannabe = (@i)
GROUP BY
...
ORDER BY
...
OPTION(OPTIMIZE FOR(@i = ''' + @tmp + '''))'
EXEC SP_EXECUTESQL @SQL, N'@i NVARCHAR(255)', @i = @tmp

how to assign the string variable in select query

Based on your code snippet and the fact that you have tagged "BPM" it appears that you are asking this question about executing SQL in the product IBM BPM. While you don't call out the version of the product I will assume it is 7.5 or later.

Assuming all of this is correct you should be executing the query using the service "SQL Execute Statement" from the System Data Toolkit. You are attempting to create the full SQL statement instead of leveraging the parameterized query capability (which would do what @The Impaler is recommending)

You need to modify your SQL so that it can use the parameterized option. The code should be something like -

tw.local.sql="select CS.ORIGINALCHECK_ID AS checkId, "+
"CS.CHECKTYPE_ID AS checkTypeId, "+
"CS.CHECKTYPE_ID AS componentID, "+
"CT.NAME AS componentName "+
"from CHECK_SUMMARY CS INNER JOIN CHECKTYPE CT "+
"ON CT.ID=CS.CHECKTYPE_ID "+
"where CS.ORIGINALCHECK_ID=? and CS.CASE_ID=?";

You would then set the parameter value with something like the following -

tw.local.parameters = new tw.object.listOf.SQLParameter();
tw.local.parameters[0] = new tw.object.SQLParameter();
tw.local.parameters[0].value = tw.local.subBarCode;
tw.local.parameters[0].type = 'VARCHAR'; //Note: Guessing here.
tw.local.parameters[1] = new tw.object.SQLParameter();
tw.local.parameters[1].value = tw.local.caseID;
tw.local.parameters[1].type = 'INTEGER';

You would then hand those both into the SQL Execute Statement service and everything should just work. You are protected from SQL injection, and won't magically break if one of your variables has say a ' in the string.

Sometimes you can get away without specifying the types, depending on the default behavior of the underlying JDBC Driver. The SQLParameter Business Object documents the possible inputs for the type.

How to declare a variable in BigQuery and set it as an argument for EXTERNAL_QUERY?

You can make it work by using EXECUTE IMMEDIATE as in below example

DECLARE req STRING;
SET req = '"SELECT * FROM tracker"';
EXECUTE IMMEDIATE 'SELECT * FROM EXTERNAL_QUERY("tdv3.eu.tracker",' || req || ');'

Stored procedure table-valued variable without aliases in query string must declare scalar variable

There are several things wrong with the approach you currently have, as I and others have commented, Brent Ozar has a good reference on dynamic SQL https://www.brentozar.com/sql/dynamic/

I would say don't pass in some SQL, construct it in the stored proc; passing in parameters such as name which is used in the where, hence I have put a full working example. This also shows how to pass the user defined table type into the stored proc and then also pass it into the dynamic SQL.

I hope this is a good enough example of the techniques, I had a bit of time so thought I would try and help as much as possible :)

/*
--------------------------------------------
Create a test table to run the stored proc against
*/
IF (NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'MyTestTable'))
BEGIN
    PRINT 'Creating table MyTestTable'

    CREATE TABLE [dbo].[MyTestTable](
        Id BIGINT NOT NULL IDENTITY(1,1) PRIMARY KEY,
        [Name] NVARCHAR(50) NOT NULL
    )

    INSERT INTO dbo.MyTestTable ([Name])
    VALUES ('Andrew'), 
    ('Bob'), 
    ('john')
    
    -- SELECT * FROM MyTestTable

END
GO

/*
--------------------------------------------
Create the table type that we pass into the store proc
*/

IF NOT EXISTS (SELECT * FROM sys.types WHERE is_table_type = 1 AND name = 'FlowStatus')
BEGIN
    PRINT 'Creating type [dbo].[FlowStatus]'
    CREATE TYPE [dbo].FlowStatus AS TABLE (
        MyId BIGINT PRIMARY KEY, 
        SomeText NVARCHAR(200)
    )
END 
GO

/*
--------------------------------------------
Create the stored proc with the User Defined table type
*/
CREATE OR ALTER PROCEDURE [dbo].[MyStoredProc]
(
    @SortBy VARCHAR(50),
    @SearchName VARCHAR(50),
    @Values dbo.FlowStatus READONLY
)
AS
BEGIN
    -- As your SQL gets more complex it is an idea to create seperate parts of the SQL
    DECLARE @SqlToExecute NVARCHAR(MAX)

    -- The primary data you want to get
    SET @SqlToExecute = N'
        SELECT T.Id, T.[Name], V.SomeText 
        FROM MyTestTable AS T
        LEFT JOIN @Values AS V ON V.MyId = T.Id 
        WHERE 1 = 1' -- We do this so that we can have many AND statements which could be expanded upon

    IF @SearchName IS NOT NULL
    BEGIN
        SET @SqlToExecute = @SqlToExecute + N'
        AND T.[Name] LIKE ''%' + @SearchName + ''''
    END

    IF @SortBy IS NOT NULL
    BEGIN
        SET @SqlToExecute = @SqlToExecute + N'
        ORDER BY ' + 
        CASE WHEN @SortBy LIKE 'Name%' THEN N'T.[Name]'
        ELSE N'T.[Id]'
        END
    END

    -- Print out the script that will be run, useful for debugging you code
    PRINT @SqlToExecute

    EXEC sp_executesql @SqlToExecute,
        N'@Values dbo.FlowStatus READONLY', @Values
    
END
GO

/*
--------------------------------------------
Now lets test it
-- Test Andrew
*/
DECLARE @flowStatusType AS dbo.FlowStatus

INSERT INTO @flowStatusType(MyId, SomeText)
VALUES(1, 'Test1'),
(2, 'Test2')

EXEC [dbo].[MyStoredProc] @SearchName = 'Andrew', @SortBy = 'Name', @Values = @flowStatusType
GO

-- Test Bob
DECLARE @flowStatusType AS dbo.FlowStatus

INSERT INTO @flowStatusType(MyId, SomeText)
VALUES(1, 'Test1'),
(2, 'Test2')

EXEC [dbo].[MyStoredProc] @SearchName = 'Bob', @SortBy = 'Name', @Values = @flowStatusType
GO

Its also worth noting that if you can just join on the @Values without needing dynamic SQL then that is sure to be less work.

Classic ASP query string variable is both set and unset

Use <%= which is a shortcut for <% Response.Write

<%If CInt(Request.QueryString("OpenYouthHistory")) > 0 Then %>
<script>
    var yid = <%=Request.QueryString("OpenYouthHistory") %>;
    window.open("YouthHistory.asp?YouthID=" + yid);
</script>
<% End If %>

Android Java Query String Dynamic Values after Declare Variable

SOLVED:

protected String sqlInsert = "INSERT INTO categories (title) VALUES (%s) ";
public boolean insert()
{
    String sql = String.format(sqlInsert, this.getTitle());
    return this.RunQuery(sql);
}

Thank You, Friends!

---

Related Topics