alter Procedure Search
@strSearch VarChar(100)
AS
Declare @SQL VarChar(1000)
Select @SQL = 'SELECT * FROM ASSET_MASTER '
Select @SQL = @SQL + @strSearch
Exec ( @SQL)
GO
exec search 'where ASSET_NAME like ''monit%'''
Thursday, May 7, 2009
Dynamic SQL
In a follow up to the Introduction to Dynamic SQL we take you through some of the tricks and pitfalls of this technique. We'll cover Create Table, sp_executesql, permissions, scope and more.
Welcome to Part 2 of my Introduction to Dynamic SQL.
In part one I explained what Dynamic SQL is and how to use it. In this article I will show some more useful applications for it and a few tricks.
The IN Clause
The IN clause is a good example of a use for Dynamic SQL. A lot of SQL Server developers use ASP or a similar web scripting language.
If in an asp page you have a Select list with multiple allowed values, the value of request.form("myList") on the processing page might look like this "1,3,4,6".
So we try to write a stored proc around this
Create Procedure Search
@strIDs VarChar(100)
AS
SELECT *
FROM
Products
WHERE
ProductID in (@strIDs)
GOOooops! No Go.
This will work
Create Procedure Search
@strIDs VarChar(100)
AS
Declare @SQL VarChar(1000)
Select @SQL = 'SELECT * FROM Products '
Select @SQL = @SQL + 'WHERE ProductID in (' + @strIDs +')'
Exec ( @SQL)
GON.B. This can also be solved using a technique like this.
Aliases
Giving a table or column a dynamic alias is a use for dynamic SQL.
This will not work
Select UserName FROM Table as @AliasThis will
Exec('Select UserName FROM Table as ' @Alias) DDL
A common question asked of SQL Team is "How do I write a stored procedure that will create a table/database. I want to pass in the name"
SQL Server will not allow this
Create Table @TableName (
ID int NOT NULL Primary Key,
FieldName VarChar(10)
)Once again, dynamic SQL to the rescue
Declare @SQL VarChar(1000)
SELECT @SQL = 'Create Table ' + @TableName + '('
SELECT @SQL = @SQL + 'ID int NOT NULL Primary Key, FieldName VarChar(10))'
Exec (@SQL)Similarly, the code to create a database would look like this:
Exec('Create Database ' + @myDBName)sp_executesql
sp_executesql is a system stored procedure that you can use in place of "exec" to execute your dynamic sql.
This allows you to have parameters in your dynamic query and pass them in. The end result is that SQL Server will try to cache the execution plan for your query giving you some of the advantages of a fully compiled query.
An example
Declare @SQL nVarChar(1000) --N.B. string must be unicode for sp_executesql
SELECT @SQL = 'SELECT * FROM pubs.DBO.Authors WHERE au_lname = @AuthorName'
Exec sp_executesql @SQL, N'@AuthorName nVarChar(50)', @AuthorName = 'white'The first parameter here is the SQL statement, then you must declare the parameters, after that you pass the in parameters as normal, comma separated.
sp_executesql is also useful when you want to execute code in another database as it will run code in the context of it's database, rather than the one it was called from.
Try this from a database that is not Pubs
Create View pubs.dbo.Auths AS (SELECT au_id, au_lname, au_fname FROM Authors)You will get this error: 'CREATE VIEW' does not allow specifying the database name as a prefix to the object name.
So you build the dynamic sql, then run it in Pub's copy of sp_executesql
I.E.
Declare @SQL nVarChar(1000)
Select @SQL = 'Create View Auths AS (SELECT au_id, au_lname, au_fname FROM Authors)'
Execute pubs.dbo.sp_executesql @sqlPermissions
When executing dynamic SQL from a stored procedure, keep in mind that the SQL is executed in the permission context of the user, not the calling procedure. This means that if your user has no rights to the tables, only to the procedure, you may run into problems.
Scope
When you run dynamic sql, it runs in it's own scope.
This
exec('set rowcount 3')
Select * from Authors
exec('set rowcount 0')Will have no effect on the result set returned from Authors. This is because by the rowcount statements have gone out of scope by the time the Select occurs.
This would be solved by this
exec('set rowcount 3 Select * from Authors Set rowcount 0')Declaring variables inside a dynamic SQL batch will also not be available outside the batch and vice versa. As a result, this would also not work.
declare @i int
Exec ('Select @i = 1')Temp tables can be used to interact between batches of standard SQL and dynamic SQL. A temp table created within a dynamic SQL batch will be destroyed when the batch completes, however a temp table created before the batch will be available to it.
Create Table #tempauth(
au_id VarChar(100),
au_fname VarChar(100),
au_lname VarChar(100)
)
declare @SQL VarChar(1000)
Select @SQL = 'Insert into #tempauth Select au_id, au_fname, au_lname FROM Authors'
exec(@SQL)
Select * from #tempauth
drop table #tempauth
Welcome to Part 2 of my Introduction to Dynamic SQL.
In part one I explained what Dynamic SQL is and how to use it. In this article I will show some more useful applications for it and a few tricks.
The IN Clause
The IN clause is a good example of a use for Dynamic SQL. A lot of SQL Server developers use ASP or a similar web scripting language.
If in an asp page you have a Select list with multiple allowed values, the value of request.form("myList") on the processing page might look like this "1,3,4,6".
So we try to write a stored proc around this
Create Procedure Search
@strIDs VarChar(100)
AS
SELECT *
FROM
Products
WHERE
ProductID in (@strIDs)
GOOooops! No Go.
This will work
Create Procedure Search
@strIDs VarChar(100)
AS
Declare @SQL VarChar(1000)
Select @SQL = 'SELECT * FROM Products '
Select @SQL = @SQL + 'WHERE ProductID in (' + @strIDs +')'
Exec ( @SQL)
GON.B. This can also be solved using a technique like this.
Aliases
Giving a table or column a dynamic alias is a use for dynamic SQL.
This will not work
Select UserName FROM Table as @AliasThis will
Exec('Select UserName FROM Table as ' @Alias) DDL
A common question asked of SQL Team is "How do I write a stored procedure that will create a table/database. I want to pass in the name"
SQL Server will not allow this
Create Table @TableName (
ID int NOT NULL Primary Key,
FieldName VarChar(10)
)Once again, dynamic SQL to the rescue
Declare @SQL VarChar(1000)
SELECT @SQL = 'Create Table ' + @TableName + '('
SELECT @SQL = @SQL + 'ID int NOT NULL Primary Key, FieldName VarChar(10))'
Exec (@SQL)Similarly, the code to create a database would look like this:
Exec('Create Database ' + @myDBName)sp_executesql
sp_executesql is a system stored procedure that you can use in place of "exec" to execute your dynamic sql.
This allows you to have parameters in your dynamic query and pass them in. The end result is that SQL Server will try to cache the execution plan for your query giving you some of the advantages of a fully compiled query.
An example
Declare @SQL nVarChar(1000) --N.B. string must be unicode for sp_executesql
SELECT @SQL = 'SELECT * FROM pubs.DBO.Authors WHERE au_lname = @AuthorName'
Exec sp_executesql @SQL, N'@AuthorName nVarChar(50)', @AuthorName = 'white'The first parameter here is the SQL statement, then you must declare the parameters, after that you pass the in parameters as normal, comma separated.
sp_executesql is also useful when you want to execute code in another database as it will run code in the context of it's database, rather than the one it was called from.
Try this from a database that is not Pubs
Create View pubs.dbo.Auths AS (SELECT au_id, au_lname, au_fname FROM Authors)You will get this error: 'CREATE VIEW' does not allow specifying the database name as a prefix to the object name.
So you build the dynamic sql, then run it in Pub's copy of sp_executesql
I.E.
Declare @SQL nVarChar(1000)
Select @SQL = 'Create View Auths AS (SELECT au_id, au_lname, au_fname FROM Authors)'
Execute pubs.dbo.sp_executesql @sqlPermissions
When executing dynamic SQL from a stored procedure, keep in mind that the SQL is executed in the permission context of the user, not the calling procedure. This means that if your user has no rights to the tables, only to the procedure, you may run into problems.
Scope
When you run dynamic sql, it runs in it's own scope.
This
exec('set rowcount 3')
Select * from Authors
exec('set rowcount 0')Will have no effect on the result set returned from Authors. This is because by the rowcount statements have gone out of scope by the time the Select occurs.
This would be solved by this
exec('set rowcount 3 Select * from Authors Set rowcount 0')Declaring variables inside a dynamic SQL batch will also not be available outside the batch and vice versa. As a result, this would also not work.
declare @i int
Exec ('Select @i = 1')Temp tables can be used to interact between batches of standard SQL and dynamic SQL. A temp table created within a dynamic SQL batch will be destroyed when the batch completes, however a temp table created before the batch will be available to it.
Create Table #tempauth(
au_id VarChar(100),
au_fname VarChar(100),
au_lname VarChar(100)
)
declare @SQL VarChar(1000)
Select @SQL = 'Insert into #tempauth Select au_id, au_fname, au_lname FROM Authors'
exec(@SQL)
Select * from #tempauth
drop table #tempauth
Friday, May 1, 2009
Tuesday, April 21, 2009
date time format
DateTime.Today - returns the current date.
DateTime.Now - returns the current time.
You can use the following methods to convert a date object to a string.
ToShortDateString()
ToLongDateString()
ToShortTimeString()
ToLongTimeString()
Custom formatting for datetime
The ToString() method of the DateTime class can be used to format the
date & time the way you want.
ToString() method of the DateTime class takes a 'formatter string' as
parameter. Here you can specify the format you want. There are various
formatting patterns you can use to format the date and time.
string formattedDate;
// Format : 07 / 03 / 2004
formattedDate = DateTime.Now.ToString("dd / MM / yyyy");
// Format : 7 / 3 / 2004 (without the preceding zeroes)
formattedDate = DateTime.Now.ToString("d / M / yyyy");
// Format : 07 / Mar / 2004
formattedDate = DateTime.Now.ToString("dd / MMM / yy");
// How to get the name of the day ?
// Format : Wednesday
formattedDate = DateTime.Today.DayOfWeek.ToString();
Formatting Patterns
The following list shows the various patterns that can be used to
convert the datetime object to custom formatted string. The patterns are
case-sensitive; for example, "MM" is different from "mm".
If the custom pattern contains white-space characters or characters
enclosed in single quotation marks, the output string will also contain
those characters. Characters not part of a format pattern or not format
characters are reproduced 'as it is'.
Pre-defined Format Patterns and Description (From the MSDN Documentation)
d The day of the month. Single-digit days will not have a leading
zero.
dd The day of the month. Single-digit days will have a leading zero.
ddd The abbreviated name of the day of the week, as defined in AbbreviatedDayNames.
dddd The full name of the day of the week, as defined in DayNames.
M The numeric month. Single-digit months will not have a leading zero.
MM The numeric month. Single-digit months will have a leading zero.
MMM The abbreviated name of the month, as defined in AbbreviatedMonthNames.
MMMM The full name of the month, as defined in MonthNames.
y The year without the century. If the year without the century is less than 10,
the year is displayed with no leading zero.
yy The year without the century. If the year without the century is less than 10,
the year is displayed with a leading zero.
yyyy The year in four digits, including the century.
gg The period or era. This pattern is ignored if the date to be formatted does
not have an associated period or era string.
h The hour in a 12-hour clock. Single-digit hours will not have a leading zero.
hh The hour in a 12-hour clock. Single-digit hours will have a leading zero.
H The hour in a 24-hour clock. Single-digit hours will not have a leading zero.
HH The hour in a 24-hour clock. Single-digit hours will have a leading zero.
m The minute. Single-digit minutes will not have a leading zero.
mm The minute. Single-digit minutes will have a leading zero.
s The second. Single-digit seconds will not have a leading zero.
ss The second. Single-digit seconds will have a leading zero.
f The fraction of a second in single-digit precision. The remaining digits are truncated.
ff The fraction of a second in double-digit precision. The remaining digits are truncated.
fff The fraction of a second in three-digit precision. The remaining digits are truncated.
ffff The fraction of a second in four-digit precision. The remaining digits are truncated.
fffff The fraction of a second in five-digit precision. The remaining digits are truncated.
ffffff The fraction of a second in six-digit precision. The remaining digits are truncated.
fffffff The fraction of a second in seven-digit precision. The remaining digits are truncated.
t The first character in the AM/PM designator defined in AMDesignator or PMDesignator, if any.
tt The AM/PM designator defined in AMDesignator or PMDesignator, if any.
z The time zone offset ("+" or "-" followed by the hour only). Single-digit hours will
not have a leading zero. For example, Pacific Standard Time is "-8".
zz The time zone offset ("+" or "-" followed by the hour only). Single-digit hours
will have a leading zero. For example, Pacific Standard Time is
"-08".
zzz The full time zone offset ("+" or "-" followed by the hour and minutes). Single-digit
hours and minutes will have leading zeros. For example, Pacific Standard Time is "-08:00".
: The default time separator defined in TimeSeparator.
/ The default date separator defined in DateSeparator.
% c - Where c is a format pattern if used alone. The "%" character can be omitted if the
format pattern is combined with literal characters or other format patterns.
\ c - Where c is any character. Displays the character literally. To display the backslash
character, use "\\".
DateTime.Now - returns the current time.
You can use the following methods to convert a date object to a string.
ToShortDateString()
ToLongDateString()
ToShortTimeString()
ToLongTimeString()
Custom formatting for datetime
The ToString() method of the DateTime class can be used to format the
date & time the way you want.
ToString() method of the DateTime class takes a 'formatter string' as
parameter. Here you can specify the format you want. There are various
formatting patterns you can use to format the date and time.
string formattedDate;
// Format : 07 / 03 / 2004
formattedDate = DateTime.Now.ToString("dd / MM / yyyy");
// Format : 7 / 3 / 2004 (without the preceding zeroes)
formattedDate = DateTime.Now.ToString("d / M / yyyy");
// Format : 07 / Mar / 2004
formattedDate = DateTime.Now.ToString("dd / MMM / yy");
// How to get the name of the day ?
// Format : Wednesday
formattedDate = DateTime.Today.DayOfWeek.ToString();
Formatting Patterns
The following list shows the various patterns that can be used to
convert the datetime object to custom formatted string. The patterns are
case-sensitive; for example, "MM" is different from "mm".
If the custom pattern contains white-space characters or characters
enclosed in single quotation marks, the output string will also contain
those characters. Characters not part of a format pattern or not format
characters are reproduced 'as it is'.
Pre-defined Format Patterns and Description (From the MSDN Documentation)
d The day of the month. Single-digit days will not have a leading
zero.
dd The day of the month. Single-digit days will have a leading zero.
ddd The abbreviated name of the day of the week, as defined in AbbreviatedDayNames.
dddd The full name of the day of the week, as defined in DayNames.
M The numeric month. Single-digit months will not have a leading zero.
MM The numeric month. Single-digit months will have a leading zero.
MMM The abbreviated name of the month, as defined in AbbreviatedMonthNames.
MMMM The full name of the month, as defined in MonthNames.
y The year without the century. If the year without the century is less than 10,
the year is displayed with no leading zero.
yy The year without the century. If the year without the century is less than 10,
the year is displayed with a leading zero.
yyyy The year in four digits, including the century.
gg The period or era. This pattern is ignored if the date to be formatted does
not have an associated period or era string.
h The hour in a 12-hour clock. Single-digit hours will not have a leading zero.
hh The hour in a 12-hour clock. Single-digit hours will have a leading zero.
H The hour in a 24-hour clock. Single-digit hours will not have a leading zero.
HH The hour in a 24-hour clock. Single-digit hours will have a leading zero.
m The minute. Single-digit minutes will not have a leading zero.
mm The minute. Single-digit minutes will have a leading zero.
s The second. Single-digit seconds will not have a leading zero.
ss The second. Single-digit seconds will have a leading zero.
f The fraction of a second in single-digit precision. The remaining digits are truncated.
ff The fraction of a second in double-digit precision. The remaining digits are truncated.
fff The fraction of a second in three-digit precision. The remaining digits are truncated.
ffff The fraction of a second in four-digit precision. The remaining digits are truncated.
fffff The fraction of a second in five-digit precision. The remaining digits are truncated.
ffffff The fraction of a second in six-digit precision. The remaining digits are truncated.
fffffff The fraction of a second in seven-digit precision. The remaining digits are truncated.
t The first character in the AM/PM designator defined in AMDesignator or PMDesignator, if any.
tt The AM/PM designator defined in AMDesignator or PMDesignator, if any.
z The time zone offset ("+" or "-" followed by the hour only). Single-digit hours will
not have a leading zero. For example, Pacific Standard Time is "-8".
zz The time zone offset ("+" or "-" followed by the hour only). Single-digit hours
will have a leading zero. For example, Pacific Standard Time is
"-08".
zzz The full time zone offset ("+" or "-" followed by the hour and minutes). Single-digit
hours and minutes will have leading zeros. For example, Pacific Standard Time is "-08:00".
: The default time separator defined in TimeSeparator.
/ The default date separator defined in DateSeparator.
% c - Where c is a format pattern if used alone. The "%" character can be omitted if the
format pattern is combined with literal characters or other format patterns.
\ c - Where c is any character. Displays the character literally. To display the backslash
character, use "\\".
Tuesday, April 14, 2009
icon on internet explorer & tag
<link rel="shortcut icon" href="http://www.rsbtechnologies.com/images/favicon.ico" type="image/x-icon" />
Thursday, April 9, 2009
retrive image from database
try
{
con = new SqlConnection(constr);
cmd = new SqlCommand("select photopath,Photo from employees where employeeid=14", con);
con.Open();
dr = cmd.ExecuteReader();
while(dr.Read())
{
if (!dr.IsDBNull(1))
{
byte[] photo = (byte[])dr[1];
MemoryStream ms = new MemoryStream(photo);
pictureBox1.Image = Image.FromStream(ms);
}
}
}
catch (Exception ex)
{
dr.Close();
cmd.Dispose();
con.Close();
MessageBox.Show(ex.Message);
}
{
con = new SqlConnection(constr);
cmd = new SqlCommand("select photopath,Photo from employees where employeeid=14", con);
con.Open();
dr = cmd.ExecuteReader();
while(dr.Read())
{
if (!dr.IsDBNull(1))
{
byte[] photo = (byte[])dr[1];
MemoryStream ms = new MemoryStream(photo);
pictureBox1.Image = Image.FromStream(ms);
}
}
}
catch (Exception ex)
{
dr.Close();
cmd.Dispose();
con.Close();
MessageBox.Show(ex.Message);
}
Subscribe to:
Posts (Atom)