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

Max Length for Varchar Datatype Variable in T-SQL

There is a limit on how long a varchar datatype variable can be. It is 8,000. It obviously not enough since this kind of variable generally used dynamical SQL, especially when use with loop statement to add up with the query statement.

The solution? Don't concatenate it. Instead, execute it often and save into a WIP temporary tables. Sure, you would need to programmatically delete these temporary tables at the end of your procedure.

http://www.fotia.co.uk/fotia/DY.13.VarCharMax.aspx

Turning SSRS Report Into Form

The difference between report and form is report pulls data out from database and the form is used to feed data into database. SQL Server Reporting Services is of the report. However, sometimes the two-way interactions are really needed. Here is an example. A parameter need to be input by user; however, if the user had input previously, it should be stored in database and no longer require user to input again.

The logic of the solution is:

1. Get value A of the subject from database according to other parameters.
2. If (Null!=A) then show report based on A, and exit.
3. Else if user input parameter B, then insert B into database, then show report based on B, and exit.
4. Else show the blank report with notice that user needs to key-in the B.

However, this logic does not allow front end to alter the what was already in database. Any change will be manully operated by back end. Alternatively, following logic can be applied:

1. If user input parameter B, then insert or alter B in database, then show report based on B, and exit.
2. Else get value A of the subject from database according to other parameters.
3. If (Null!=A) then show report based on A, and exit.
4. Else show the blank report with notice that user needs to key-in the B.

As a standard procedure, the user therefore is not required to key-in the parameter unless is notified.

INSERT and SET query can be as text or as stored procedure. However, INSERT query should not be stand along as text, or an error message will be generated. At the end, SSRS is of report.

Schedule A Database Job In MySQL

As of MySQL 5.1, one can use event to schedule a job:

CREATE EVENT event_name
ON SCHEDULE schedule
DO event_body
;

schedule can be: AT timestamp [+ INTERVAL interval] ...
or EVERY interval
[STARTS timestamp [+ INTERVAL interval] ...]
[ENDS timestamp [+ INTERVAL interval] ...]

interval can be: quantity followed by unit
unit can be: {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |
WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |
DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}

event_body can be SQL query or EXECUTE sp_name.

For instance:
CREATE EVENT event_name
ON SCHEDULE EVERY 5 MINUTE
DO SELECT * FROM tab
;

ALTER, DROP and SET GLOBAL event_name = 1 to switch on the event, etc.


http://dev.mysql.com/doc/refman/5.1/en/create-event.html
http://answers.oreilly.com/topic/177-how-to-use-events-to-schedule-database-actions-in-mysql/
http://rpbouman.blogspot.com/2005/10/scheduling-procedure-execution-in.html

Add A Linked Server In SQL Server

The system stored procedure sp_addlinkedserver can be used to created a linked server represent a remote SQL Server. This also can be used when SQL query needs to access multiple databases located in different servers.

Only following parameters are essential:

EXEC sp_addlinkedserver @server = 'RemoteComputerName' -- Remote computer name.
, @srvproduct = '' -- Can be nothing.
, @provider = 'SQLNCLI' -- SQL Server Driver, if do not know, use this.
, @datasrc = 'ServerName' -- Server name and instance.
;

If the remote SQL Server does not have an instance name, then the @datasrc parameter needs only to contain the remote server name and not the instance. There is not place for your nominated name.

To establish the access:

EXEC sp_addlinkedsrvlogin 'RemoteComputerName', 'true';

This would created linked server for all database users. When use it, just like this:

SELECT *
FROM RemoteComputerName.DatabaseName.dbo.TableName
GO


http://msdn.microsoft.com/en-us/library/ms190479.aspx
http://sqlserverplanet.com/dba/using-sp_addlinkedserver/
http://blogs.msdn.com/b/sql_protocols/archive/2006/08/10/694657.aspx

Passing Multiple-Value Parameter To Oracle Procedure

Scenario:

Client wants to add new parameter - Transaction Reason.

At beginning, they want a drop down listing all the transaction reasons and ask for multiple value input.

As there are near 150 transaction reasons for claim, it is not a good way to list all the transaction reasons. For the drop down, it needs to create a view for select the reasons. And if new reason is added, we need to update the list. There will be a large maintenance work.

After further discuss with clients, no drop down for new parameter has reached the agreement: Letting the client enter transaction reason code. And allow multiple value input.

Analysis:

Since the report is from a stored procedure, the parameter of that procedure is automatically added to report as report parameter. In this case, one should add new parameter in procedure and use the parameter in ‘where’ statement.

However, Crystal Reports’ multiple-value parameter setting is disabled when report come from a stored procedure. Crystal will pass the multiple-value inputs as a string, such as transaction code: ’70, MO, MA, 40’.

The best possible solution is: Passing a comma-delimited string as one parameter and then parsing it in the stored procedure.

There are three methods to achieve this: using temporary table to store the after-parsing input list; and using table type to store the after-parsing input list. It is noticed however, when using table type (collection), it is hard to select a row as from a real table. On other hand, temporary table will slow the system. The third method is utilizing the Oracle built-in XML functions.

Solution:

Using Oracle existing XML functions:

• In order to use those XML functions, input string must in format delimited by comma:

'333,444,aaa'

• Replace the ',' with XML tag:

lv_Transaction_Reason_Code := '' Replace(Upper(Trim(Transaction_Reason_Code)), ',', '') '';

• Use XML functions to parse string and save into a table type collection.:

CREATE OR REPLACE TYPE TP_multi_value is Table Of VARCHAR2(10) Not Null;
lv_multi_value TP_multi_value;
Select Trim(t.EXTRACT('id/text()'))
Bulk Collect
Into lv_multi_value
From Table( XMLSequence(xmltype(lv_Transaction_Reason_Code).extract('//id'))) t
Where t.EXTRACT('id/text()') Is Not Null;

• In the ref_cursor use Table() function to change collection to a table for select from:
And
(lv_Transaction_Reason_Code Is Null
Or
tr.TRANSACTION_REASON_CODE In (Select t.column_value From Table (lv_multi_value) t)
) ;

• In report, add text to ask user enter code separated by ','.

• Need to show the selected transaction reason code in the report title. To do so, need to select correct formatted input string in back end:

lv_Reason_Code Varchar2(1000);
lv_Reason_Code := Upper(TRIM(Transaction_Reason_Code));
Open op_ObjCursor For
Select lv_StartDate StartDate
, lv_EndDate EndDate
, lv_Policy_number lv_Policy_number
, lv_Reason_Code Reason_Code
……

Then in report, show the code in title using formula:

Local StringVar sReasonCode;
If Isnull({PR_PAYMENTSRECOVERIES.REASON_CODE}) Then
sReasonCode := "All"
Else
sReasonCode := {PR_PAYMENTSRECOVERIES.REASON_CODE};
"Transaction Reason Code: " + sReasonCode + chr(13) +


Important tips:

• Trim the space from original input string;
• Only insert into collection the value is not null;
• Trim the space of each individual item before save into collection.
• Those XML function is deal with string. If need number, need To_number() as last.
• In report, edit parameter. Enter promoting text: Use comma to separate the code. For example: MA,70,40. (However, the promote text could not be saved with report. It is only one time show. This is the bug for Crystal X.)
• To display the code list, format the string at backend, then select this string available in cursor.

Stored Procedure: Pivot With Dynamic Columns

Following is a stored procedure in aims to improve Microsoft built-in function of PIVOT in SQL Server. To use PIVOT, one must know exact number and name of columns after pivot, which does not meet the real world’s need. What we need is the pivot function works with dynamic pivot columns. To use this stored procedure, you would need to create a temporary table first with name of “Temp_Transform_Input”. Following is the structure of the this table:

Column 1, can be any name and data type. It would remain intact as it was after pivot.
Column 2, must be named as “Pivot_Column” and should be VARCHAR data type. Its contents after automatically distinct would be pivoted as column titles after execute this procedure.
Column 3, must be named as “Content_Column” and should be INT data type. It would be summed up after pivot. If the purpose is to count, put 1 here for every record.

To use it, just type EXECUTE P_Transform GO. The result will be automatically saved into 7 temporary tables. You do not need to manage the garbage collection for these two temporary tables, this procedure will manage them for you.

Why use temporary table instead of use Multi-statement Table-Valued Function as output method? Since you are dealing with the dynamic columns, it is impossible to declare the return table in Multi-statement Table-Valued Function.

CREATE PROCEDURE P_Transform
AS
BEGIN
IF EXISTS( SELECT *
FROM SYS.TABLES
WHERE NAME = 'Temp_Transform_Row_Largest'
)
DROP TABLE dbo.Temp_Transform_Row_Largest
IF EXISTS( SELECT *
FROM SYS.TABLES
WHERE NAME = 'Temp_Transform_Row_Sum'
)
DROP TABLE dbo.Temp_Transform_Row_Sum
IF EXISTS( SELECT *
FROM SYS.TABLES
WHERE NAME = 'Temp_Transform_Column_Largest'
)
DROP TABLE dbo.Temp_Transform_Column_Largest
IF EXISTS( SELECT *
FROM SYS.TABLES
WHERE NAME = 'Temp_Transform_Column_Sum'
)
DROP TABLE dbo.Temp_Transform_Column_Sum
IF EXISTS( SELECT *
FROM SYS.TABLES
WHERE NAME = 'Temp_Transform_Both_Largest'
)
DROP TABLE dbo.Temp_Transform_Both_Largest
IF EXISTS( SELECT *
FROM SYS.TABLES
WHERE NAME = 'Temp_Transform_Both_Sum'
)
DROP TABLE dbo.Temp_Transform_Both_Sum
IF EXISTS( SELECT *
FROM SYS.TABLES
WHERE NAME = 'Temp_Transform_Result'
)
DROP TABLE dbo.Temp_Transform_Result
DECLARE @FirstColumn VARCHAR(128)
DECLARE @Summary VARCHAR(512)
DECLARE @Columns VARCHAR(MAX)
DECLARE @Query VARCHAR(MAX)
SELECT @FirstColumn = COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Temp_Transform_Input'
AND ORDINAL_POSITION = 1
SET @Summary =
'
SELECT ' + @FirstColumn + '
, MAX(Content_Column) AS Largest
INTO Temp_Transform_Row_Largest
FROM Temp_Transform_Input
GROUP BY ' + @FirstColumn + '
ORDER BY MAX(Content_Column) DESC
, ' + @FirstColumn + '
'
EXECUTE(@Summary)
SET @Summary =
'
SELECT ' + @FirstColumn + '
, SUM(Content_Column) AS Total
INTO Temp_Transform_Row_Sum
FROM Temp_Transform_Input
GROUP BY ' + @FirstColumn + '
ORDER BY SUM(Content_Column) DESC
, ' + @FirstColumn + '
'
EXECUTE(@Summary)
SET @Summary =
'
SELECT Pivot_Column
, MAX(Content_Column) AS Largest
INTO Temp_Transform_Column_Largest
FROM Temp_Transform_Input
GROUP BY Pivot_Column
ORDER BY MAX(Content_Column) DESC
, Pivot_Column
'
EXECUTE(@Summary)
SET @Summary =
'
SELECT Pivot_Column
, SUM(Content_Column) AS Total
INTO Temp_Transform_Column_Sum
FROM Temp_Transform_Input
GROUP BY Pivot_Column
ORDER BY SUM(Content_Column) DESC
, Pivot_Column
'
EXECUTE(@Summary)
SET @Summary =
'
SELECT ' + @FirstColumn + '
, Pivot_Column
, MAX(Content_Column) AS Largest
INTO Temp_Transform_Both_Largest
FROM Temp_Transform_Input
GROUP BY ' + @FirstColumn + '
, Pivot_Column
ORDER BY MAX(Content_Column) DESC
, Pivot_Column
'
EXECUTE(@Summary)
SET @Summary =
'
SELECT ' + @FirstColumn + '
, Pivot_Column
, SUM(Content_Column) AS Total
INTO Temp_Transform_Both_Sum
FROM Temp_Transform_Input
GROUP BY ' + @FirstColumn + '
, Pivot_Column
ORDER BY SUM(Content_Column) DESC
, ' + @FirstColumn + '
, Pivot_Column
'
EXECUTE(@Summary)
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)
IF EXISTS( SELECT *
FROM SYS.TABLES
WHERE NAME = 'Temp_Transform_Input'
)
DROP TABLE dbo.Temp_Transform_Input
END
GO

It is very important the length of each contents in Pivot_Column must be less 30 characters, because the contents will become the column name after transpose. Else, it won't work properly.


http://www.paladn.com/component/content/article/40-sql-server/122-udf-forms.html