How to construct the query in Stored Proc

Hi,
In the past I build up the query string within VB.NET page and easy to add the filtering statements in the SQL statement since it is just a string. For example, if user selected an option then include it in the filter, otherwise, just return all rows from table:
Codes in ASPX.VB:
Dim SQL as String="SELECT * From Table1"
IF UserOption <> Null then
SQL = SQL & " WHERE Column1=" UserOption"
End if

Now, since I have a complicated page which need to use Stored Procedure to manapulate a temporary table before the final result. But I found when I want to add some user options similar to above, I found I don't know how to do it in Stored Procedure. In the Stored Procedure Property screen, I can't insert a IF..THEN statement within a SELECT statement. Seems I can only check the user option first and then determine the SELECT statement to use. That is: IF UserOption THEN SELECT statement 1 ELSE SELECT statement 2.
But it is impossible for me to do this way since I'm not only one user option on the page. User usually can have several filters/selections on his screen. So if check which user option(s) are selected and write a static SELECT statement for it, I will have to program a complicated store procedure to cater all combinations for all user options (where some options may be null).
Hope you can understanding what my mean and give me advices.
Regards,
Raymond

Hi,
you can use in Store Proc query dinamic creation :
CREATE PROCEDURE XXXX

@.OPTION1 INT

AS

DECLARE @.MYQUERY VARCHAR(1000)
DECLARE @.MYFILTER VARCHAR(1000)

SET @.MYQUERY = 'SELECT * FROM Table1 WHERE WHERE ID <> 0'

IF @.OPTION1 <> ''
BEGIN
SET @.MYFILTER = ' AND NegozioNome = ' + CONVERT(VARCHAR(20),@.OPTION1)
END

EXEC (@.MYQUERY + @.MYFILTER)

|||

I n SQL Server you can use the CASE statement, try the links below for sample code. Hope this helps.
http://www.craigsmullins.com/ssu_0899.htm

http://www.4guysfromrolla.com/webtech/102704-1.shtml

|||

Thanks a lot! I will study it. I also find this structure may help, which introducedd in another thread in this forum:

AND ([DD1 Industry Code] LIKE @.FilterIndustries OR @.FilterIndustries ='')

|||

scu wrote:

Hi,
you can use in Store Proc query dinamic creation :
CREATE PROCEDURE XXXX

@.OPTION1 INT

AS

DECLARE @.MYQUERY VARCHAR(1000)
DECLARE @.MYFILTER VARCHAR(1000)

SET @.MYQUERY = 'SELECT * FROM Table1 WHERE WHERE ID <> 0'

IF @.OPTION1 <> ''
BEGIN
SET @.MYFILTER = ' AND NegozioNome = ' + CONVERT(VARCHAR(20),@.OPTION1)
END

EXEC (@.MYQUERY + @.MYFILTER)

Will it affect the performance when the query is built up in a dynamic string rather than build up within stored procedure using CASE/IF..THEN..ELSE/COALESCE statements? It is important concern to the effectiveness when the store procedure contains complicated queries.|||Hi,
I've used many times dinamic queries in Store Proc.
The performance's difference between dinamic queries and equivalent 'static' storec proc is imperceptible.
|||

scu wrote:

Hi,
I've used many times dinamic queries in Store Proc.
The performance's difference between dinamic queries and equivalent 'static' storec proc is imperceptible.

Thanks for the kindly sharing from all of you!|||You will get better performance out of a dynamic SQL statement than you would using CASE statements.
See this link for more information on dynamic T-SQL and different ways to handle your query:
http://www.sommarskog.se/dyn-search.html

Nick|||

nick-w wrote:

You will get better performance out of a dynamic SQL statement than you would using CASE statements.
See this link for more information on dynamic T-SQL and different ways to handle your query:
http://www.sommarskog.se/dyn-search.html

Nick

Great article! Thanks Nick!

how to construct dynamic sql in stored procedure

Hi Guys.

I want to construct a SQL in stored procedure in the following way. Please guide me how to achive this

CREATE PROCEDURE P_SAMPLE

(

@.P_ONE NVARCHAR(240)

)

AS

BEGIN

DECLARE

@.TMP_CNT INT

BEGIN

EXEC ('SELECT @.TMP_CNT = 1')

-- PRINT @.TMP_CNT

END

END

Regards

Mani

Use sp_executesql to pass/retrive the data to/from dynamic sql. Exec won’t help you here.

Code Snippet

DECLARE @.TMP_CNT INT

DECLARE @.SQL as NVarchar(4000)

DECLARE @.Params as NVarchar(4000)

Set @.SQL = N'SELECT @.TMP_CNT = 1'

Set @.Params = N'@.TMP_CNT as Int OUTPUT'

Exec sp_executesql @.SQL, @.Params, @.TMP_CNT OUTPUT

Select @.TMP_CNT

|||

here is one of the best resource in Dynamic sql

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

Madhu

How to Concatenate Strings That Have Trailing Spaces?

I am trying to export data from a SQLServer database into a text file
using a stored procedure. I want to be able to read it and debug it
easily; therefore, I want all the columns to indent nicely. This means
I need to append trailing spaces to a text string (such as "Test1 ")
or append leading space in front of a text string that contains a
number (such as " 12.00"). Now, the stored procedure works fine when
I run it in Query Analyzer. But it doesn't work correctly when I run
it using ISQL - All the columns are not indented. I am wondering why
it doesn't work in ISQL.

This is what I want, and this is also what I get when I run the stored
procedure using Query Analyzer:

Test1 , 2,Test1.txt , 1.00, 1.00
Test22 , 2,Test22.txt , ,
Test333 , 2,Test333.txt , 30.00, 30.00

This is what I get if I run the stored procedure using ISQL
(isql -S myserver -E -w 556 -h-1 -n -d mydb -Q "exec MyTest"):

Test1, 2,Test1.txt, 1.00, 1.00
Test22, 2,Test22.txt, ,
Test333, 2,Test333.txt, 30.00, 30.00

You can see that the result from ISQL has the following differences:
1. It puts a space in front of each row.
2. It appends enough spaces at the end of each line to make
the line length to be exactly 61 characters.
3. It gets rid of the trailing space from each column.
4. It leaves only one blank space if the column has nothing
but a serie of spaces.

The following is the stored procedure that I am testing:

create procedure MyTest
as

set nocount on

create table #Test
(
Field1 varchar(10) null,
Field2 varchar( 5) null,
Field3 varchar(20) null,
Field4 varchar(10) null,
Field5 varchar(10) null
)
insert into #Test values
( "Test1 ", " 2","Test1.txt ", " 1.00", " 1.00" )
insert into #Test values
( "Test22 ", " 2","Test22.txt ", " ", " " )
insert into #Test values
( "Test333 ", " 2","Test333.txt ", " 30.00", " 30.00" )

select Field1 + "," +
Field2 + "," +
Field3 + "," +
Field4 + "," +
Field5
from #Test

drop table #Test
go

Strangely, the differences #3 and #4 only show up when I use the
SELECT statement on a table. They don't show up when I use SELECT
statements to show constant text strings or string variables, like
this:

set nocount on
select "Test1 " + "," +
" 2" + "," +
"Test1.txt " + "," +
" 1.00" + "," +
" 1.00"
select "Test22 " + "," +
" 2" + "," +
"Test22.txt " + "," +
" " + "," +
" "
select "Test333 " + "," +
" 2" + "," +
"Test333.txt " + "," +
" 30.00" + "," +
" 30.00"

The result is like the following if I use constant text strings or
string variables:

Test1 , 2,Test1.txt , 1.00, 1.00

Test22 , 2,Test22.txt , ,

Test333 , 2,Test333.txt , 30.00, 30.00

I need to run it from ISQL because that is how I run _all_ my other
stored procedures. I don't want to do anything differently just
because I need to run this stored procedure.

Thanks in advance for any suggestion.

Jay Chan"Jay Chan" <jaykchan@.hotmail.com> wrote in message
news:c7e5acb2.0308120646.2c8593ec@.posting.google.c om...
> I am trying to export data from a SQLServer database into a text file
> using a stored procedure. I want to be able to read it and debug it
> easily; therefore, I want all the columns to indent nicely. This means
> I need to append trailing spaces to a text string (such as "Test1 ")
> or append leading space in front of a text string that contains a
> number (such as " 12.00"). Now, the stored procedure works fine when
> I run it in Query Analyzer. But it doesn't work correctly when I run
> it using ISQL - All the columns are not indented. I am wondering why
> it doesn't work in ISQL.

Check out the 'SET ANSI_PADDING' setting in BOL.

Ian.|||Jay Chan (jaykchan@.hotmail.com) writes:
> I am trying to export data from a SQLServer database into a text file
> using a stored procedure. I want to be able to read it and debug it
> easily; therefore, I want all the columns to indent nicely. This means
> I need to append trailing spaces to a text string (such as "Test1 ")
> or append leading space in front of a text string that contains a
> number (such as " 12.00"). Now, the stored procedure works fine when
> I run it in Query Analyzer. But it doesn't work correctly when I run
> it using ISQL - All the columns are not indented. I am wondering why
> it doesn't work in ISQL.

This is because with ISQL you get a different setting for ANSI_PADDING.
This setting is OFF by default with ISQL, but ON by default with Query
Analyzer. The effect of this setting is that if it is OFF, SQL Server
trims trailing spaces from varchar data when you insert it.

Use SET ANSI_PADDING ON in ISQL to get ISQL to behave like QA.

The setting is actually saved with the table column, so if you create
the table in QA, it should work in ISQL even with the setting off. (But
I have not tested this.)

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> Check out the 'SET ANSI_PADDING' setting in BOL.

Thanks! This switch fixes the problem very nicely. Now, all the
columns indent correctly.

Jay Chan|||> This is because with ISQL you get a different setting for ANSI_PADDING.
> This setting is OFF by default with ISQL, but ON by default with Query
> Analyzer. The effect of this setting is that if it is OFF, SQL Server
> trims trailing spaces from varchar data when you insert it.
> Use SET ANSI_PADDING ON in ISQL to get ISQL to behave like QA.

Yes, you are right. I add the switch in my stored procedure right
before it starts creating temporary tables, and now it can indent the
column of info quite nicely. Thanks.

> The setting is actually saved with the table column, so if you create
> the table in QA, it should work in ISQL even with the setting off. (But
> I have not tested this.)

I believe this is correct according to the Help info on that switch. I
also create tables in Query Analyzer; this explains why I haven't come
across this problem until now.

Jay Chan

How to compress text before storing in DB?

I was planning on running a service where thousands of text messages are stored. Obviously I'd want to make the most of my DB space, and was wondering if there's some way for SQL to compress text down to the smallest space possible. If not, is there some kind of ASP component I could download to do this? Failing that, I could always write a simple one, which takes the most common letter combinations, and shortens them down to a single character.

Any advice?Space is cheap. Use it. Otherwise, your data will be dependent on whatever compression algorithm you use, and everyone will have to consult with you to read the data. Of course, if you're a consultant focused on billable hours, writing some hair-brained compression may be just what you want.

Either don't worry about space (have you done the math on what this gives you vs. buying the adequate space?), or store your text outside the db on a compressed drive.|||I don't have to consult with anyone :) It's a one-man project, just something I'm building for consumers, there's no income involved. And space is cheap, yes, if I'm hosting this solution myself. But I'm not. And SQL Server 2000 DB storage does not come cheaply at a good, reliable host.|||Anyone else? Anyone?|||Maybe this will get you to drink the water: Video on Hotmail Architecture (http://channel9.msdn.com/ShowPost.aspx?PostID=39016)

How to compare SQL server stored procedures from two separate databases?

Is there a straight forward way or a tool to compare SQL server database objects, such as stored procedures located in separate databases?

Well, the way that I always do it is to use the RedGate SQL Compare tool. www.red-gate.com. It works very nice and will compare everything, including permissions.

There are other ways, and Buck Woody (who I have seen around the forums) has an article here that covers it pretty well:

http://www.informit.com/guides/content.asp?g=sqlserver&seqNum=210&rl=1

|||You can use Visual Studio Team System(VSTS) for Database professional. It is covered all the features of readgate and something extra.|||

I use the easiest/dumbest/cheapest way

Get SPs from both databases, and use free version of examdiff to compare them (or you can use Visual SourceSafe, etc..)

|||

There are several free tools that do this. The one that I use is SQL Effects Clarity CE.

It compares tables, stored procedures, views, functions, users, roles, etc.

You can download it here: http://www.sqleffects.com/sfxClarityDetails.html

Hope this helps.

How to commit a transaction even when trigger fails

I have a row that, when updated, triggers a stored procedure. However, if this stored procedure fails, the row is never updated at all.

I know with SQL 2000, you can specify on on_event argument of AFTER so that the transaction is actually "committed" before the trigger goes off, but is there a similar way to do this with SQL 7? I can't seem to find a way to have the "trigger" action take effect regardless if whatever action the trigger causes fails or not.

EdCould you issue a commit right before calling the sp?

How to combine two columns into one in an sql

I have an stored procedure that returns 3 columns. Month, Date, and Total Number of Calls.

Here is the stored Proc:

SELECTDATEPART(mm, CALLSTARTTIME) , DATEPART(dd, CALLSTARTTIME),COUNT(*)

FROMCALL_LOG_MASTER

WHERE(COMMERCIALS = '1')AND(PINCODEIS NOT NULL)

GROUP BY DATEPART(mm, CALLSTARTTIME),DATEPART(dd, CALLSTARTTIME)

ORDER BY DATEPART(mm, CALLSTARTTIME),DATEPART(dd, CALLSTARTTIME)

It returns a table:

MONTH DATE TOTAL NUMBER OF CALLS
======= ===== ===========
1 1 10

1 2 15

My question is: is it possible to combine the Month and Date column into one column. e.g.

Date Total Number of Calls
==== ==============
1/1 10
1/2 15

Please Help, Thanks in advance :)

SELECT CONVERT(nvarchar(2),DATEPART(mm, CALLSTARTTIME)) + '/' + CONVERT(nvarchar(2),DATEPART(dd, CALLSTARTTIME)) AS [Date],COUNT(*)

FROMCALL_LOG_MASTER

WHERE(COMMERCIALS = '1')AND(PINCODEIS NOT NULL)

GROUP BY DATEPART(mm, CALLSTARTTIME)) + '/' + CONVERT(nvarchar(2),DATEPART(dd, CALLSTARTTIME))

ORDER BY DATEPART(mm, CALLSTARTTIME),DATEPART(dd, CALLSTARTTIME)

|||

thx, I did some configuration. works like a charm

SELECT CONVERT(nvarchar(2),DATEPART(mm, CALLSTARTTIME)) + '/' +CONVERT(nvarchar(2),DATEPART(dd, CALLSTARTTIME))AS[Date],COUNT(*)

FROMCALL_LOG_MASTER

WHERE(COMMERCIALS = '1')AND(PINCODEIS NOT NULL)

GROUP BY DATEPART(mm, CALLSTARTTIME) ,DatePart(dd, Callstarttime),CONVERT(nvarchar(2),DATEPART(mm, CALLSTARTTIME)) + '/' +CONVERT(nvarchar(2),DATEPART(dd, CALLSTARTTIME))

ORDER BY DATEPART(mm, CALLSTARTTIME),DATEPART(dd, CALLSTARTTIME)

How to comair DB objects in Development server with Production server

I have couple of Stored procedures and UDFs and Tables which I generated an
script from our development server and our database admin has run that
script on the production server.Unfortunately because of some changes I had
to generate the script two more times and now I'd like to make sure that the
objects in the producation are same as development.Is there a way to compaur
tham and to see what the potential differences are?
ThanksThere are many database comparison tools in the market. For example:
redgate.com
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"J-T" <J-T@.nospam.com> wrote in message
news:O3FEPJ8jFHA.1204@.TK2MSFTNGP12.phx.gbl...
>I have couple of Stored procedures and UDFs and Tables which I generated an
>script from our development server and our database admin has run that
>script on the production server.Unfortunately because of some changes I had
>to generate the script two more times and now I'd like to make sure that
>the objects in the producation are same as development.Is there a way to
>compaur tham and to see what the potential differences are?
>
> Thanks
>|||http://www.aspfaq.com/2209
Also, think about WinDiff if you have Visual Studio installed...
"J-T" <J-T@.nospam.com> wrote in message
news:O3FEPJ8jFHA.1204@.TK2MSFTNGP12.phx.gbl...
>I have couple of Stored procedures and UDFs and Tables which I generated an
>script from our development server and our database admin has run that
>script on the production server.Unfortunately because of some changes I had
>to generate the script two more times and now I'd like to make sure that
>the objects in the producation are same as development.Is there a way to
>compaur tham and to see what the potential differences are?

How to comair Database objects

I have couple of Stored procedures and UDFs and Tables which I generated an
script from our development server and our database admin has run that
script on the production server.Unfortunately because of some changes I had
to generate the script two more times and now I'd like to make sure that the
objects in the producation are same as development.Is there a way to compaur
tham and to see what the potential differences are?
Thanks
Hi
Using a version control system should stop this occuring. You can use dmo to
compare objects or possibly a third party application such as dbghost
www.dbghost.com or red gate compare www.red-gate.com .
John
"J-T" <J-T@.nospam.com> wrote in message
news:%23Jr%238I8jFHA.3048@.TK2MSFTNGP10.phx.gbl...
>I have couple of Stored procedures and UDFs and Tables which I generated an
>script from our development server and our database admin has run that
>script on the production server.Unfortunately because of some changes I had
>to generate the script two more times and now I'd like to make sure that
>the objects in the producation are same as development.Is there a way to
>compaur tham and to see what the potential differences are?
>
> Thanks
>
|||The red-gate is awesome.
Thanks a lot
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:u6Z%23yP8jFHA.3144@.TK2MSFTNGP12.phx.gbl...
> Hi
> Using a version control system should stop this occuring. You can use dmo
> to compare objects or possibly a third party application such as dbghost
> www.dbghost.com or red gate compare www.red-gate.com .
> John
> "J-T" <J-T@.nospam.com> wrote in message
> news:%23Jr%238I8jFHA.3048@.TK2MSFTNGP10.phx.gbl...
>

How to comair Database objects

I have couple of Stored procedures and UDFs and Tables which I generated an
script from our development server and our database admin has run that
script on the production server.Unfortunately because of some changes I had
to generate the script two more times and now I'd like to make sure that the
objects in the producation are same as development.Is there a way to compaur
tham and to see what the potential differences are?
ThanksHi
Using a version control system should stop this occuring. You can use dmo to
compare objects or possibly a third party application such as dbghost
www.dbghost.com or red gate compare www.red-gate.com .
John
"J-T" <J-T@.nospam.com> wrote in message
news:%23Jr%238I8jFHA.3048@.TK2MSFTNGP10.phx.gbl...
>I have couple of Stored procedures and UDFs and Tables which I generated an
>script from our development server and our database admin has run that
>script on the production server.Unfortunately because of some changes I had
>to generate the script two more times and now I'd like to make sure that
>the objects in the producation are same as development.Is there a way to
>compaur tham and to see what the potential differences are?
>
> Thanks
>|||The red-gate is awesome.
Thanks a lot
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:u6Z%23yP8jFHA.3144@.TK2MSFTNGP12.phx.gbl...
> Hi
> Using a version control system should stop this occuring. You can use dmo
> to compare objects or possibly a third party application such as dbghost
> www.dbghost.com or red gate compare www.red-gate.com .
> John
> "J-T" <J-T@.nospam.com> wrote in message
> news:%23Jr%238I8jFHA.3048@.TK2MSFTNGP10.phx.gbl...
>

How to comair Database objects

I have couple of Stored procedures and UDFs and Tables which I generated an
script from our development server and our database admin has run that
script on the production server.Unfortunately because of some changes I had
to generate the script two more times and now I'd like to make sure that the
objects in the producation are same as development.Is there a way to compaur
tham and to see what the potential differences are?
ThanksHi
Using a version control system should stop this occuring. You can use dmo to
compare objects or possibly a third party application such as dbghost
www.dbghost.com or red gate compare www.red-gate.com .
John
"J-T" <J-T@.nospam.com> wrote in message
news:%23Jr%238I8jFHA.3048@.TK2MSFTNGP10.phx.gbl...
>I have couple of Stored procedures and UDFs and Tables which I generated an
>script from our development server and our database admin has run that
>script on the production server.Unfortunately because of some changes I had
>to generate the script two more times and now I'd like to make sure that
>the objects in the producation are same as development.Is there a way to
>compaur tham and to see what the potential differences are?
>
> Thanks
>|||The red-gate is awesome.
Thanks a lot
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:u6Z%23yP8jFHA.3144@.TK2MSFTNGP12.phx.gbl...
> Hi
> Using a version control system should stop this occuring. You can use dmo
> to compare objects or possibly a third party application such as dbghost
> www.dbghost.com or red gate compare www.red-gate.com .
> John
> "J-T" <J-T@.nospam.com> wrote in message
> news:%23Jr%238I8jFHA.3048@.TK2MSFTNGP10.phx.gbl...
>>I have couple of Stored procedures and UDFs and Tables which I generated
>>an script from our development server and our database admin has run that
>>script on the production server.Unfortunately because of some changes I
>>had to generate the script two more times and now I'd like to make sure
>>that the objects in the producation are same as development.Is there a way
>>to compaur tham and to see what the potential differences are?
>>
>> Thanks
>

How to code an aspx page to run a stored procedure with a parameter

My stored proceddure "My Programs" includes a parameter @.meid.
How do I code an aspx file to run the procedure with a user ID, e.g. EmpID?
I tried the following codes, but the error message indicated it can not find
the Stored Procedure. How do I pass the EmpID as a Stored Procedure parameter?

<%
meid = EmpId
cmd.CommandText ="MyPrograms meid"
cmd.CommandType = CommandType.StoredProcedure
cmd.Connection = sqlConnection2
sqlConnection2.Open()
reader3 = cmd.ExecuteReader(meid)
While reader3.Read()
sb.Append(reader3(i).ToString() +".....<BR> <BR /> ")
EndWhile
%>

TIA,
Jeffrey

Check if this helps:http://dotnetjunkies.com/WebLog/dinakar/articles/74220.aspx

|||

Thanks.

My co-worker told me I am supposed not need to know the parameter name, "@.meid".
Then what does "Failed to convert parameter value from a String to a Int32" mean?
Why the ExecuteReader() needs to convert EmpID to Int32? It's laready an integer.

TIA,
Jeffrey

<%
cmd.CommandText ="MyPrograms"
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add(New SqlParameter("@.meid", SqlDbType.Int))
cmd.Parameters("@.meid").Value =EmpID
cmd.Connection = sqlConnection2
sqlConnection2.Open()
reader3 = cmd.ExecuteReader() Failed to convert parameter value from a String to a Int32.]

While reader3.Read()

sb.Append(reader3(i).ToString() +".....<BR> <BR /> ")

EndWhile

|||

I think I have solved the problem. The reason for the rror: "Failed to convert parameter value from a String to a Int32"
is Value = "EmpID". It should be EmpID.

Thanks gain.

How to check which tables were called?

Hello everyone:
I have some nightly jobs that execute stored procedure to call the tables? I want to know which table are called by these stored procedures. Is it possible? Any idea will be appreciated.
Thanks
ZYTLemme get this right, you have a series of stored procedures that run every night, but want a list of the affected tables? basically a listing of the tables in those stored procedures run at night?

Basically, in stead of reading through the SP's and noting the tables used, you want an automation process?|||Yes, I need a table list that are affected in the nightly job/|||Just do sp_depends on all of the stored procedures

USE Northwind
GO

CREATE PROC mySproc99
AS
SELECT * FROM Orders
GO

EXEC sp_depends mySproc99
GO

DROP PROC mySproc99
GO|||...although be aware that this will not show tables referenced in dynamic SQL statements embedded in your procedures.|||True..and if the tables was dropped and recreated it won't show as well..

How about adding some triggers