CodeSnip: How to Create a Complete Dynamic SQL Statement Using SQL Server
page 3 of 5
by Nidal Arabi
Average Rating: 
Views (Total / Last 10 Days): 24984/ 61


An example of calling the stored procedure is provided below.  It can select the first 5 rows from the table called Products and order them by the ProductName field after filtering for the product name that starts with the letter A from the Northwind database.  You can copy the code in Listing 2 directly to an SQL Server Query analyzer.

Listing 2

Use Northwind
Exec DynamicSql 5,
 'ProductName, SupplierID,QuantityPerUnit','Products','Order By ProductName','Where ProductName like ''A%'''

The following result is generated:

ProductName SupplierID QuantityPerUnit

Alice Mutton 7 20 - 1 kg tins

Aniseed Syrup 1 12 - 550 ml bottles

Another example with the usage of the output parameter is presented below:

Exec DynamicSql 1,
'@ProdName = ProductName','Products',null,'Where ProductName like ''A%''','@ProdName', 'varchar'

This example would return the string "Alice Mutton" to the calling part.

View Entire Article

User Comments

Title: hello   
Name: asa
Date: 2012-11-17 7:52:29 AM
Title: q   
Name: q
Date: 2012-09-11 9:54:43 AM
Title: Dynamic sql   
Name: mala
Date: 2009-10-23 8:23:59 AM
very very nice article.....

this article help to me....thanks a lot!..

Community Advice: ASP | SQL | XML | Regular Expressions | Windows

©Copyright 1998-2021  |  Page Processed at 2021-12-02 8:01:13 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search