Dynamic SQL in T-SQL

First of all, the dynamic SQL is not part of the stored procedure, but constitutes its own scope. So, it is a independent thread. Your task indeed is to pass the bind variable and/or parameter into that thread and retrieve the result back to your stored procedure.

There are two ways to invoke dynamic SQL, EXEC sp_executesql and EXEC(). EXEC() is simple and useful when SQL Server version is older than 2005. Since EXEC only permits string literals and string variables to be concatenated and not arbitrary expressions, and since you cannot use parameters, you cannot as easily get values out from EXEC(). Let us make it easier: Never use EXEC() in stored procedure. Let DBA use it for their tasks.

In EXEC sp_executesql, the first built-in parameter @stmt is the SQL query statement in string. It is best to declare it as NVARCHAR datatype. So does for the second built-in parameter @params.

If your own parameter is of column name and table name, please use this way: quotename(@column_or_table_name). Else, it won't work. By doing this, you indeed is to embed column name and table name as bind variable into the SQL script. It thus no longer the parameters of built-in stored procedure sp_executesql. So, please declare it outside the sp_executesql, and do not include it in the built-in parameter @params.

To get the query result out from the sp_executesql, in your SQL query statement, the following SELECT style is necessary:

SET @sqlStatement = 'SELECT @avgResult = AVG(' + quotename(@Column_Name) + ' ) FROM Tab_Name'

In addition, your own parameter for sp_executesql needs to be declared as OUTPUT.

After first two built-in parameters for sp_executesql, you will need to assign values to your own parameters for sp_executesql.

Okey, here is an example:

DECLARE @result INT
DECLARE @EachColumn VARCHAR(30)
DECLARE curs CURSOR FOR
SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Temp_Transform_Result'
OPEN curs
FETCH NEXT FROM curs INTO @EachColumn
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @exeString NVARCHAR(2000)
SET @exeString = ' SELECT @result = CONVERT(INT, AVG(' + quotename(@EachColumn) + '))
FROM Temp_Transform_Result
'
EXEC sp_executesql @exeString
, N'@result INT OUTPUT'
, @result OUTPUT
SET @exeString = ' INSERT INTO Temp_Transform_Input
VALUES ( ''IQM''
, @EachColumn
, @result
)
'
EXEC sp_executesql @exeString
, N'@EachColumn VARCHAR(30)
,@result INT
'
, @EachColumn = @EachColumn
, @result = @result
FETCH NEXT FROM curs INTO @EachColumn
END
CLOSE curs
DEALLOCATE curs

In first sp_executesql, @EachColumn is an embed bind variable rather than parameters. On contrast, in second sp_executesql, it is parameter. You may note, same parameter names used for sp_executesql and outsider stored procedure. So you see

, @EachColumn = @EachColumn
, @result = @result

The left hand side is parameter for sp_executesql, and the right hand side is the variable for stored procedure.


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

Set Vs Select in T-SQL

Select will allow more than one records to to assigned to variable:

SELECT @var = singleColumn
FROM tab

Set either allow one record, or generate an error while the original value of variable remains unchanged:

SET @var =
( SELECT singleColumn
FROM tab
)


http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/05/18/449.aspx

Delete or Select Records Which Is In Or Not In A View

MySQL

Delete records in a table based on the condition that keys of that table is in a another view or is not in the view will not work in MySQL, because either IN or NOT IN needs to be followed by a string delimited by comma. Sure, you can program it to turn a view into a string by insert comma in it. Following is an example which does not work in MySQL:

DELETE FROM tab
WHERE tab.id IN
( SELECT tab.id
FROM tab
WHERE ...
)

An easier way is to use INNER JOIN method to accomplish the task. Here is how to do this:

DELETE tab.* FROM tab
INNER JOIN
( SELECT tab.id
FROM tab
WHERE ...
) AS vCondition
ON vCondition.joinKey = tab.joinKey

Please remember between DELETE and FROM you would need to insert what is going to be deleted. In this case, it is tab.*, represents all fields in the records which meets the condition.

For NOT IN condition, you would need to make sure what is going to be joined would be opposite to NOT IN condition:

DELETE tab.* FROM tab
INNER JOIN
( SELECT tab.id
FROM tab
WHERE tab.id NOT IN
( SELECT tab.id
FROM tab
WHERE ...
)) AS vCondition
ON vCondition.joinKey = tab.joinKey

Don't ask why this time NOT IN works. If you do, please ask MySQL developers directly why they make life so hard. At the end, it is free. So, would you please shut up?

T-SQL

Delete or select records in a table based on the condition that keys of that table is in a another view or is not in the view will work in SQL Server. However, if that is dynamic T-SQL, it won't work. Following is the solution:

SELECT @Columns = COALESCE( @Columns + ',[' + CAST(LTRIM(RTRIM(Pivot_Column)) AS VARCHAR) + ']'
, '[' + CAST(LTRIM(RTRIM(Pivot_Column)) AS VARCHAR)+ ']'
)
FROM
( SELECT DISTINCT Pivot_Column
FROM Temp_Transform_Input
) AS Base_Q
SET @Query = ' SELECT *
INTO Temp_Transform_Result
FROM
( SELECT *
FROM Temp_Transform_Input
) AS Inner_Q
PIVOT
( SUM(Content_Column)
FOR Pivot_Column IN (' + @Columns + ')
) AS Pivot_Q
'
EXECUTE(@Query)


http://www.electrictoolbox.com/article/mysql/cross-table-delete/

Code Conventions: Microsoft Access SQL

1. Use original column name as much as possible until last presentative SELECT. This is to help for visual tracing back to where the data come from originally.

2. Never use format [something] as alias for the column, it would cause the visual confusion when you changed idea to reuse it, or extent to next level of expansion. You may use it at last presentative SELECT.

3. Always please comma (,) in front rather than at end of a line. It would enable you to easily conduct visual check, since all lines are started from a command or comma. For instance:

SELECT Col_1
, Col_2
FROM Tab
;

4. Always use your notepad or SQL editor to edit and save file. When complete, copy it onto Microsoft Access. This is because Microsoft Access will rewrite your scripts to make your life much harder. Every time when you need to edit it, open your text file to edit it and then paste it onto Access.

5. Doing your formatting job as much as possible in Access rather than at later stage at Excel or other presentation layer. It is even more important when you make your report automation.