Parameter passing to a stored procedure

Hey huys, I got this stored procedure. First of all: could this work?

--start :)
CREATE PROCEDURE USP_MactchUser

@.domainUserID NVARCHAR(50) ,

@.EmployeeID NVARCHAR(50) ,

@.loginType bit = '0'
AS

INSERT INTO T_Login

(employeeID, loginType, domainUserID)

Values
(@.EmployeeID, @.loginType, @.domainUserID)
GO
--end :)

then I got this VB.Net code in my ASP.net page...

--begin :)
Private Sub matchUser()
Dim insertMatchedUser As SqlClient.SqlCommand
Dim daMatchedUser As SqlClient.SqlDataAdapter
SqlConnection1.Open()
'conn openned
daMatchedUser = New SqlClient.SqlDataAdapter("USP_MatchUser", SqlConnection1)
daMatchedUser.SelectCommand.CommandType = CommandType.StoredProcedure
daMatchedUser.SelectCommand.Parameters.Add(New SqlClient.SqlParameter("@.EmployeeID", SqlDbType.NVarChar, 50))
daMatchedUser.SelectCommand.Parameters.Add(New SqlClient.SqlParameter("@.domainUserID", SqlDbType.NVarChar, 50))
daMatchedUser.SelectCommand.Parameters("@.EmployeeID").Value = Trim(lblEmployeeID.Text)
daMatchedUser.SelectCommand.Parameters("@.domainUserID").Value = Trim(lblDomainuserID.Text)
daMatchedUser.SelectCommand.Parameters("@.EmployeeID").Direction = ParameterDirection.Output
daMatchedUser.SelectCommand.Parameters("@.domainUserID").Direction = ParameterDirection.Output
SqlConnection1.Close()
'conn closed

End Sub
--

If I try this it doesn't work (maybe that's normal :) ) Am I doing it wrong. The thing is, in both label.text properties a values is stored that i want to as a parameter to my stored procedure. What am I doing wrong?no.

Private Sub matchUser()
Dim insertMatchedUser As SqlClient.SqlCommand

insertMatchedUser = new SqlCommand;
insertMatchedUser.CommandText = "USP_MactchUser"
insertMatchedUser.CommandType = CommandType.StoredProcedure
insertMatchedUser.Parameters.Add(New SqlClient.SqlParameter("@.EmployeeID", SqlDbType.NVarChar, 50))
insertMatchedUser.Parameters.Add(New SqlClient.SqlParameter("@.domainUserID", SqlDbType.NVarChar, 50))
insertMatchedUser.Parameters("@.EmployeeID").Value = lblEmployeeID.Text.Trim()
insertMatchedUser.Parameters("@.domainUserID").Value = lblDomainuserID.Text.Trim()

insertMatchedUser.Connection = SqlConnection1;
SqlConnection1.Open()
'conn openned
insertMatcheduser.ExecuteNonQuery()

insertMatchedUser.Dispose()
SqlConnection1.Dispose()
End Sub|||Also:

CREATE PROCEDURE USP_MactchUser
@.domainUserID NVARCHAR(50) ,
@.EmployeeID NVARCHAR(50) ,
@.loginType bit = 0
AS

the bit data type literal should not have single quotes.sql

Parameter Number is Invalid

I am using Crystal Reports 8.0, with SQL Server.
I am trying to create a Report on a Stored Procedure.
My Stored Procedure is having 3 parameters (2 datetime parameters and 1 integer parameter).
When i select the stored procedure in the initial stage, it asks for the parameters. I tried entering 2050-01-01 00:00:00.000, etc etc ...

But I am getting an error "Parameter Number 1 is invalid" which is the same datetime field.

When I run the stored procedure in the query analyser. Its runs fine:
exec spTravelRpt_Person_chargeno '01-01-1990 00:00:00.000','01-01-2050 00:00:00.000',-1.

Can anyone plzzzz plzzz help me.. bcoz i am stuck.. i cannot move forward..

Ur help will be appreicated.Give only 2050-01-01 to datetime field and when calling the SP from your front end, pass the parameter in the format you want|||Hi Madhi,

I tried giving that too ... just
2050-01-01 ...
but it didnt help me...
Its giving the same error:
Parameter number 1 is invalid.
What do i do ????|||change the parameters datatype to varchar(20) instead of datetime and check.sql

Parameter multi-value problem when using a stored procedure

Hi folks,
I am describing the issue as below:
1. create a stored procedure as below
....where age in (@.p_age)
note: age is the table coumn of table table1 with datatype tinyint
2. .... and create a second dataset for parameter @.p_age
select distinct age from table1
3. associate the parameter...run it
4. There is no problem with single value. But when two ages are selected,
I got error message, "...Erro convert data type nvarchar to tinyint"
Please advise. PeterYou cannot pass and use multi-value parameters to a stored procedure and use
it in a query as you have. If that query was in RS itself then it would
work. This is not a RS thing, it is a SQL Server stored procedure issue.
Just try it from Query Analyzer and you will see what I mean. I do the
following, I create
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:74D9A9F0-B1F9-4925-8080-87FC99215462@.microsoft.com...
> Hi folks,
> I am describing the issue as below:
> 1. create a stored procedure as below
> ....where age in (@.p_age)
> note: age is the table coumn of table table1 with datatype tinyint
> 2. .... and create a second dataset for parameter @.p_age
> select distinct age from table1
> 3. associate the parameter...run it
> 4. There is no problem with single value. But when two ages are selected,
> I got error message, "...Erro convert data type nvarchar to tinyint"
> Please advise. Peter|||Try again, sent before done:
What doesn't work has nothing really to do with RS but has to do with Stored
Procedures in SQL Server. You cannot do the following in a stored procedure.
Let's say you have a Parameter called @.MyParams
Now you can map that parameter to a multi-value parameter but if in your
stored procedure you try to do this:
select * from sometable where somefield in (@.MyParams)
It won't work. Try it. Create a stored procedure and try to pass a
multi-value parameter to the stored procedure. It won't work.
What you can do is to have a string parameter that is passed as a multivalue
parameter and then change the string into a table.
This technique was told to me by SQL Server MVP, Erland Sommarskog
For example I have done this
inner join charlist_to_table(@.STO,Default)f on b.sto = f.str
So note this is NOT an issue with RS, it is strictly a stored procedure
issue.
Here is the function:
CREATE FUNCTION charlist_to_table
(@.list ntext,
@.delimiter nchar(1) = N',')
RETURNS @.tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
str varchar(4000),
nstr nvarchar(2000)) AS
BEGIN
DECLARE @.pos int,
@.textpos int,
@.chunklen smallint,
@.tmpstr nvarchar(4000),
@.leftover nvarchar(4000),
@.tmpval nvarchar(4000)
SET @.textpos = 1
SET @.leftover = ''
WHILE @.textpos <= datalength(@.list) / 2
BEGIN
SET @.chunklen = 4000 - datalength(@.leftover) / 2
SET @.tmpstr = @.leftover + substring(@.list, @.textpos, @.chunklen)
SET @.textpos = @.textpos + @.chunklen
SET @.pos = charindex(@.delimiter, @.tmpstr)
WHILE @.pos > 0
BEGIN
SET @.tmpval = ltrim(rtrim(left(@.tmpstr, @.pos - 1)))
INSERT @.tbl (str, nstr) VALUES(@.tmpval, @.tmpval)
SET @.tmpstr = substring(@.tmpstr, @.pos + 1, len(@.tmpstr))
SET @.pos = charindex(@.delimiter, @.tmpstr)
END
SET @.leftover = @.tmpstr
END
INSERT @.tbl(str, nstr) VALUES (ltrim(rtrim(@.leftover)),
ltrim(rtrim(@.leftover)))
RETURN
END
GO
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:74D9A9F0-B1F9-4925-8080-87FC99215462@.microsoft.com...
> Hi folks,
> I am describing the issue as below:
> 1. create a stored procedure as below
> ....where age in (@.p_age)
> note: age is the table coumn of table table1 with datatype tinyint
> 2. .... and create a second dataset for parameter @.p_age
> select distinct age from table1
> 3. associate the parameter...run it
> 4. There is no problem with single value. But when two ages are selected,
> I got error message, "...Erro convert data type nvarchar to tinyint"
> Please advise. Peter

Parameter doesn''t get supplied to stored procedure

I'm rather new at reporting services, so bear with me on this one.

I have a stored procedure in MSSQL called prc_failedSLA_per_week, that has two parameters (@.startWeek and @.endWeek). I've added a dataset in reporting services which runs my procedure. when I run it from the data tab in RS, I get prompted for values on these two parameters, and a table gets generated. But if I try to preview my report, I get the error "Procedure of function "prc_failedSLA_per_week" expects parameter "@.startWeek", which was not supplied".

I've tried to find info on this in the help file to no avail, not even google helps me in any way. I guess it's some kind of newbie mistake, but I simply can't figure out what to do.

Any help would be greatly appreciated!
Nevermind, I got it solved. Seem that I had to press the refresh button in the dataset for the parameters to work, which wasn't mentioned anywhere that I looked. Unlogical, but now it works at least :-/
|||same thing happened to me. I actually thought that the code was messed up (code behind). So deleted my report and recreated another one. But then i refreshed my dataset over and over and now its workin!! THANKS!!!

Parameter doesn't get supplied to stored procedure

I'm rather new at reporting services, so bear with me on this one.

I have a stored procedure in MSSQL called prc_failedSLA_per_week, that has two parameters (@.startWeek and @.endWeek). I've added a dataset in reporting services which runs my procedure. when I run it from the data tab in RS, I get prompted for values on these two parameters, and a table gets generated. But if I try to preview my report, I get the error "Procedure of function "prc_failedSLA_per_week" expects parameter "@.startWeek", which was not supplied".

I've tried to find info on this in the help file to no avail, not even google helps me in any way. I guess it's some kind of newbie mistake, but I simply can't figure out what to do.

Any help would be greatly appreciated!
Nevermind, I got it solved. Seem that I had to press the refresh button in the dataset for the parameters to work, which wasn't mentioned anywhere that I looked. Unlogical, but now it works at least :-/
|||same thing happened to me. I actually thought that the code was messed up (code behind). So deleted my report and recreated another one. But then i refreshed my dataset over and over and now its workin!! THANKS!!!

Parameter direction of a stored procedure

I am using the MS SQL Server Management Studio Express to create a stored procedure in one of my databases. I specify one of the parameters as OUTPUT as follows:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[ProcRetDbl]

@.Threshold real, @.Result real OUTPUT
AS
BEGIN

SET NOCOUNT ON;

SELECT @.Result = Channel1 FROM DataTable WHERE Channel2 < @.Threshold
END

But then when I look at the properties of the @.Result parameter in the Object Explorer's tree, it is shown as "Input/Output". Now, this seems like no problem at all since it will work fine as output, even though I don't need it to be able to do input as well, but I'm wondering why that is happening.

I am using ADO.Net on the other end to execute the procedure and I need to decide what parameter type to set to the SqlParameter object: "Output" or "InputOutput". I'm sure I can sort this out but I usually like to know what I'm doing. Thanks for the help.

KamenYou are probably thinking too hard :)

You need to supply a parameter in to give you something to read when the value comes out. I can't remember what I use for sqlParameters - probably inputoutput. Try both - what have you got to lose?|||The only pure output from a stored procedure is the return code value and any result sets. Procedure parameters must be input, and can optionally be output too.

-PatP

Parameter Conversions Question from SQL Server

I have a question regarding a casting error I recieve when I explictly assign a value to a value in a stored procedure......
The value pstrLoginName is a public string defined in a code module
The stored procedure is NVARCHAR Datatypes....

Here is the Stored Procedure

CREATE procedure dbo.Appt_GetPermissions_NET
(
@.LoginName nvarchar(15),
@.Password NvarChar(15),
@.DeleteScan bit Output

)
as
select

@.DeleteScan=UserP_DeleteScan
from
Clinic_Users
where
UserName = @.LoginName
and
UserPassword = @.Password

GO

Here is my error

Specified cast is not valid
Line 194: cmdsql.CommandType = CommandType.StoredProcedure
Line 195:
Line 196: parmLogin = cmdsql.Parameters.Add("@.LoginName", SqlDbType.NVarChar, 15).Value = pstrLoginName
Line 197: parmPassword = cmdsql.Parameters.Add("@.LoginPassword", SqlDbType.NVarChar, 15).Value = pstrLoginPassword
Line 198: parmDelete = cmdsql.Parameters.Add("@.DeleteScan", SqlDbType.Bit)

my vb .net code to execute this

Public Sub ObtainPermission()

'get the needed data
Dim consql As New SqlConnection("Server=myserver;database=APPOINTMENTS;uid=webtest;pwd=webtest")
Dim cmdsql As New SqlCommand
Dim parmLogin As SqlParameter
Dim parmPassword As SqlParameter
Dim parmDelete As SqlParameter

cmdsql = New SqlCommand("Appt_GetPermissions_NET", consql)
cmdsql.CommandType = CommandType.StoredProcedure

parmLogin = cmdsql.Parameters.Add("@.LoginName", SqlDbType.NVarChar, 15).Value = pstrLoginName <--error occurs here

parmPassword = cmdsql.Parameters.Add("@.LoginPassword", SqlDbType.NVarChar, 15).Value = pstrLoginPassword <--I'm sure it will happen here then
parmDelete = cmdsql.Parameters.Add("@.DeleteScan", SqlDbType.Bit)
parmDelete.Direction = ParameterDirection.Output

consql.Open()
cmdsql.ExecuteNonQuery()

'obtain rights

pstrPermissions = cmdsql.Parameters("@.DeleteScan").Value
consql.Close()

End SubDude, your doing too much on one line!
If you are going to do all this on one line you'll have to add some paranthesis:
(parmLogin = cmdsql.Parameters.Add("@.LoginName", SqlDbType.NVarChar, 15)).Value = pstrLoginName

I'd recommend splitting the row in two lines for better readability.|||You have both this line:

Dim cmdsql As New SqlCommand

and this line:

cmdsql = New SqlCommand("Appt_GetPermissions_NET", consql)

Note that you have used "New" twice. Remove the "New" from the first line and you should have better luck.
Terri|||Ofcourse it's wrong to allocate an object twice,
but it shouldn't raise an error, since he hasn't used the variable
before allocating it the second time.
Won't it just 'leak' an object instance?sql

parameter

Hi,

I am using a stored proc which takes in a parameter from a webpage. I am using freetext and have to pass in a parameter inside quotes so that freetext treats the phrase as a single word.

something similar to this.

freetext(SITE_NAME,' " '+ @.Search+' " ')

this is giving error.

Please Help

try using the char function passing it the numeric ascii code for double quotes in your sql statement: char(39)

|||

Hi bendJoe,

I am using freetext and have to pass in a parameter inside quotes so that freetext treats the phrase as a single word

Could you please give us an example what the @.Search parameter could be? Also, why do you want FreeText function treate your search words as a singe world?

Based on my understanding, we can pass in more than one words into FreeText predicate and those words will be seperated and assigend a weight value automatically. And that's how FreeText function is designed. See the defination in Books Online:

Is a predicate used to search columns containing character-based data types for values that match the meaning and not the exact wording of the words in the search condition. When FREETEXT is used, the full-text query engine internally performs the following actions on thefreetext_string, assigns each term a weight, and then finds the matches.

Separates the string into individual words based on word boundaries (word-breaking).

Generates inflectional forms of the words (stemming).

Identifies a list of expansions or replacements for the terms based on matches in the thesaurus.

parameter

Hi,
I have created several parameters in my report.
These parameters are drop down controls which are populated from stored
procedures.
How is it possible to populate one of these parameters based on the
selection on another parameter drop down control?
ThanksOn May 29, 9:03 am, farshad <fars...@.discussions.microsoft.com> wrote:
> Hi,
> I have created several parameters in my report.
> These parameters are drop down controls which are populated from stored
> procedures.
> How is it possible to populate one of these parameters based on the
> selection on another parameter drop down control?
> Thanks
Yes it is. I'll give you a quick example and you can base your report
of it hopefully:
DataSet1 {This is your main dataset}
Select *
from cars_inventory
where make=@.make and model=@.model {here are you two parameter filters}
DataSet2
select distinct make
from cars_inventory
DataSet3
select distinct model
from cars_inventory
where make=@.make
Make sure in your main dataset that @.make comes first in the where
clause otherwise you will run into problems. Under the menu
Report>Report Parameters go to configure your parameters. Make both
of them queried (@.make from dataset2 and @.model from dataset3). That
should work. When you preview, model will be grayed out until you
select a make, then it will only pull back makes based on that model.
So for example you select Toyota for make, then under the model drop
down you will get a list {Camry, Corolla, etc}. If you select Nissan
you'll get a different list {Altima, Maxima, etc}.
I hope that helps.

Parameter

Knowing the parameter used in a stored procedure how can I find out the stored procedure's name?create procedure test @.id int,@.id2 int
as
select @.id
go
select * from sysobjects where id in(
select id from syscolumns where name='@.id2')

parameter

Hi,
I have created several parameters in my report.
These parameters are drop down controls which are populated from stored procedures.
How is it possible to populate one of these parameters based on the selection on another parameter drop down control?
Thanks

keep your parameters in the order they are dependent. refer the parameter some thing like

select src_parameter2 where src_param1=@.param1

Thanks

Raj Deep.A

sql

paramater values inside IN clause

I have a problem in a stored proc where I pass more than one value to
the parameters inside a IN clause: it doesn=B4t retrieve any data. But
if there's only one value I get results.
here's the code:
CREATE PROCEDURE spGetInvoiceData
@.strClient varchar(10),
@.strBrand varchar(10),
@.strService varchar(10),
@.dtBeguinDate datetime,
@.dtEndDate datetime,
@.strCamp varchar(10) =3D NULL
AS
select request.orgunit as unit, campaign.name as campaign, service.name
as service, entity.name as destinationEntity, requestitem.request,
request.efectivedate, item.itemcode, item.name as item, brand.name as
brand, requestitem.amount,
item.weigth, facturationtype.name as facturacao,
facturationstep.value, facturationstep.lowerbound,
facturationstep.upperbound, facturationstep.addvalue
into #ttemp
from request, requestitem, item, brand, service, entity,
facturationtype, facturationstep, campaign
where requestitem.request in (select code from request where state=3D1
and (requeststate=3D'expd' or requeststate=3D'done' or
requeststate=3D'closed' or requeststate=3D'atrib')
and client like @.strClient and efectivedate >=3D @.dtBeguinDate and
efectivedate <=3D @.dtEndDate) and requestitem.state=3D1 and
(facturationtype.code =3D request.invoicetype
and request.destinationentity =3D entity.code
and request.service =3D service.code
and request.code=3Drequestitem.request
and item.code =3D requestitem.item
and requestitem.brand =3D brand.code
and facturationtype.code =3D facturationstep.facturationtypecode
and request.campaign=3Dcampaign.code)
and item.state=3D1 and requestitem.state=3D1 and request.state=3D1 and
service.state=3D1 and brand.state=3D1 and campaign.state=3D1
and entity.state=3D1 and facturationtype.state=3D1 and
request.toinvoice=3D1 and facturationstep.state =3D 1
and service.code in (@.strService) and brand.code in (@.strBrand)
and (request.campaign =3D @.strCamp or @.strCamp IS NULL)
order by request.efectivedate, REQUESTITEM.REQUEST,BRAND.NAME
select *,
(select distinct sum(t1.weigth * t1.amount) from #ttemp t1 where
t1.request =3D t2.request and t1.brand =3D t2.brand) as pesomarca,
(select distinct sum(weigth * amount) from #ttemp t1 where t1.request
=3D t2.request) as pesopedido
from #ttemp t2
drop table #ttemp
GO
Can anyone help?Which parameter are you using to pass in multiple values? The parameters for
the stored procedure don't seem quite 'large' enough to hold many values in
a comma delimited string.
You are passing them in as a comma delimited string?
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Pedro" <p_costa@.sapo.pt> wrote in message
news:1151080483.044837.122720@.y41g2000cwy.googlegroups.com...
I have a problem in a stored proc where I pass more than one value to
the parameters inside a IN clause: it doesnt retrieve any data. But
if there's only one value I get results.
here's the code:
CREATE PROCEDURE spGetInvoiceData
@.strClient varchar(10),
@.strBrand varchar(10),
@.strService varchar(10),
@.dtBeguinDate datetime,
@.dtEndDate datetime,
@.strCamp varchar(10) = NULL
AS
select request.orgunit as unit, campaign.name as campaign, service.name
as service, entity.name as destinationEntity, requestitem.request,
request.efectivedate, item.itemcode, item.name as item, brand.name as
brand, requestitem.amount,
item.weigth, facturationtype.name as facturacao,
facturationstep.value, facturationstep.lowerbound,
facturationstep.upperbound, facturationstep.addvalue
into #ttemp
from request, requestitem, item, brand, service, entity,
facturationtype, facturationstep, campaign
where requestitem.request in (select code from request where state=1
and (requeststate='expd' or requeststate='done' or
requeststate='closed' or requeststate='atrib')
and client like @.strClient and efectivedate >= @.dtBeguinDate and
efectivedate <= @.dtEndDate) and requestitem.state=1 and
(facturationtype.code = request.invoicetype
and request.destinationentity = entity.code
and request.service = service.code
and request.code=requestitem.request
and item.code = requestitem.item
and requestitem.brand = brand.code
and facturationtype.code = facturationstep.facturationtypecode
and request.campaign=campaign.code)
and item.state=1 and requestitem.state=1 and request.state=1 and
service.state=1 and brand.state=1 and campaign.state=1
and entity.state=1 and facturationtype.state=1 and
request.toinvoice=1 and facturationstep.state = 1
and service.code in (@.strService) and brand.code in (@.strBrand)
and (request.campaign = @.strCamp or @.strCamp IS NULL)
order by request.efectivedate, REQUESTITEM.REQUEST,BRAND.NAME
select *,
(select distinct sum(t1.weigth * t1.amount) from #ttemp t1 where
t1.request = t2.request and t1.brand = t2.brand) as pesomarca,
(select distinct sum(weigth * amount) from #ttemp t1 where t1.request
= t2.request) as pesopedido
from #ttemp t2
drop table #ttemp
GO
Can anyone help?|||For instance if pass these values:
@.strService ('111,'222') which is in the subquery won't get results
but if @.strService ('111') i get results
Arnie Rowland escreveu:
> Which parameter are you using to pass in multiple values? The parameters =
for
> the stored procedure don't seem quite 'large' enough to hold many values =
in
> a comma delimited string.
> You are passing them in as a comma delimited string?
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> "Pedro" <p_costa@.sapo.pt> wrote in message
> news:1151080483.044837.122720@.y41g2000cwy.googlegroups.com...
> I have a problem in a stored proc where I pass more than one value to
> the parameters inside a IN clause: it doesn=B4t retrieve any data. But
> if there's only one value I get results.
> here's the code:
>
> CREATE PROCEDURE spGetInvoiceData
> @.strClient varchar(10),
> @.strBrand varchar(10),
> @.strService varchar(10),
> @.dtBeguinDate datetime,
> @.dtEndDate datetime,
> @.strCamp varchar(10) =3D NULL
> AS
> select request.orgunit as unit, campaign.name as campaign, service.name
> as service, entity.name as destinationEntity, requestitem.request,
> request.efectivedate, item.itemcode, item.name as item, brand.name as
> brand, requestitem.amount,
> item.weigth, facturationtype.name as facturacao,
> facturationstep.value, facturationstep.lowerbound,
> facturationstep.upperbound, facturationstep.addvalue
> into #ttemp
> from request, requestitem, item, brand, service, entity,
> facturationtype, facturationstep, campaign
> where requestitem.request in (select code from request where state=3D1
> and (requeststate=3D'expd' or requeststate=3D'done' or
> requeststate=3D'closed' or requeststate=3D'atrib')
> and client like @.strClient and efectivedate >=3D @.dtBeguinDate and
> efectivedate <=3D @.dtEndDate) and requestitem.state=3D1 and
> (facturationtype.code =3D request.invoicetype
> and request.destinationentity =3D entity.code
> and request.service =3D service.code
> and request.code=3Drequestitem.request
> and item.code =3D requestitem.item
> and requestitem.brand =3D brand.code
> and facturationtype.code =3D facturationstep.facturationtypecode
> and request.campaign=3Dcampaign.code)
> and item.state=3D1 and requestitem.state=3D1 and request.state=3D1 and
> service.state=3D1 and brand.state=3D1 and campaign.state=3D1
> and entity.state=3D1 and facturationtype.state=3D1 and
> request.toinvoice=3D1 and facturationstep.state =3D 1
> and service.code in (@.strService) and brand.code in (@.strBrand)
> and (request.campaign =3D @.strCamp or @.strCamp IS NULL)
> order by request.efectivedate, REQUESTITEM.REQUEST,BRAND.NAME
> select *,
> (select distinct sum(t1.weigth * t1.amount) from #ttemp t1 where
> t1.request =3D t2.request and t1.brand =3D t2.brand) as pesomarca,
> (select distinct sum(weigth * amount) from #ttemp t1 where t1.request
> =3D t2.request) as pesopedido
> from #ttemp t2
>=20
> drop table #ttemp
> GO
>=20
>=20
>=20
> Can anyone help?|||For @.strService, the comma delimited string should have single quotes around
each element. Your example [@.strService ('111,'222')] is incorrect.
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Pedro" <p_costa@.sapo.pt> wrote in message
news:1151081861.177541.90980@.g10g2000cwb.googlegroups.com...
For instance if pass these values:
@.strService ('111,'222') which is in the subquery won't get results
but if @.strService ('111') i get results
Arnie Rowland escreveu:
> Which parameter are you using to pass in multiple values? The parameters
> for
> the stored procedure don't seem quite 'large' enough to hold many values
> in
> a comma delimited string.
> You are passing them in as a comma delimited string?
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> "Pedro" <p_costa@.sapo.pt> wrote in message
> news:1151080483.044837.122720@.y41g2000cwy.googlegroups.com...
> I have a problem in a stored proc where I pass more than one value to
> the parameters inside a IN clause: it doesnt retrieve any data. But
> if there's only one value I get results.
> here's the code:
>
> CREATE PROCEDURE spGetInvoiceData
> @.strClient varchar(10),
> @.strBrand varchar(10),
> @.strService varchar(10),
> @.dtBeguinDate datetime,
> @.dtEndDate datetime,
> @.strCamp varchar(10) = NULL
> AS
> select request.orgunit as unit, campaign.name as campaign, service.name
> as service, entity.name as destinationEntity, requestitem.request,
> request.efectivedate, item.itemcode, item.name as item, brand.name as
> brand, requestitem.amount,
> item.weigth, facturationtype.name as facturacao,
> facturationstep.value, facturationstep.lowerbound,
> facturationstep.upperbound, facturationstep.addvalue
> into #ttemp
> from request, requestitem, item, brand, service, entity,
> facturationtype, facturationstep, campaign
> where requestitem.request in (select code from request where state=1
> and (requeststate='expd' or requeststate='done' or
> requeststate='closed' or requeststate='atrib')
> and client like @.strClient and efectivedate >= @.dtBeguinDate and
> efectivedate <= @.dtEndDate) and requestitem.state=1 and
> (facturationtype.code = request.invoicetype
> and request.destinationentity = entity.code
> and request.service = service.code
> and request.code=requestitem.request
> and item.code = requestitem.item
> and requestitem.brand = brand.code
> and facturationtype.code = facturationstep.facturationtypecode
> and request.campaign=campaign.code)
> and item.state=1 and requestitem.state=1 and request.state=1 and
> service.state=1 and brand.state=1 and campaign.state=1
> and entity.state=1 and facturationtype.state=1 and
> request.toinvoice=1 and facturationstep.state = 1
> and service.code in (@.strService) and brand.code in (@.strBrand)
> and (request.campaign = @.strCamp or @.strCamp IS NULL)
> order by request.efectivedate, REQUESTITEM.REQUEST,BRAND.NAME
> select *,
> (select distinct sum(t1.weigth * t1.amount) from #ttemp t1 where
> t1.request = t2.request and t1.brand = t2.brand) as pesomarca,
> (select distinct sum(weigth * amount) from #ttemp t1 where t1.request
> = t2.request) as pesopedido
> from #ttemp t2
> drop table #ttemp
> GO
>
> Can anyone help?|||I ment to write @.strService ('111,222') and not
@.strService ('111,'222').
And I don't think the problem is the single quotes around each element
because my query string in VB.net is:
strQuery =3D "EXECUTE " & database & ".dbo.spGetInvoiceData '" & client &
"', '" & brandCode & "', '" & serviceCode & "', '" & startDate & "', '"
& endDate & "'"
Arnie Rowland escreveu:
> For @.strService, the comma delimited string should have single quotes aro=
und
> each element. Your example [@.strService ('111,'222')] is incorrect.
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> "Pedro" <p_costa@.sapo.pt> wrote in message
> news:1151081861.177541.90980@.g10g2000cwb.googlegroups.com...
> For instance if pass these values:
> @.strService ('111,'222') which is in the subquery won't get results
> but if @.strService ('111') i get results
>
> Arnie Rowland escreveu:|||Table DDL would help in diagnosis.
If service.code is a numeric datatype then, @.strService ('111,222') would be
correct. If service.code is a character datatype then each comma separated
element needs quotes around it.
Please verify the datatype for service.code.
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Pedro" <p_costa@.sapo.pt> wrote in message
news:1151083291.840423.149320@.r2g2000cwb.googlegroups.com...
I ment to write @.strService ('111,222') and not
@.strService ('111,'222').
And I don't think the problem is the single quotes around each element
because my query string in VB.net is:
strQuery = "EXECUTE " & database & ".dbo.spGetInvoiceData '" & client &
"', '" & brandCode & "', '" & serviceCode & "', '" & startDate & "', '"
& endDate & "'"
Arnie Rowland escreveu:
> For @.strService, the comma delimited string should have single quotes
> around
> each element. Your example [@.strService ('111,'222')] is incorrect.
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> "Pedro" <p_costa@.sapo.pt> wrote in message
> news:1151081861.177541.90980@.g10g2000cwb.googlegroups.com...
> For instance if pass these values:
> @.strService ('111,'222') which is in the subquery won't get results
> but if @.strService ('111') i get results
>
> Arnie Rowland escreveu:|||Sorry, but I' ve supllied wrong information:
@.strService('dsmt','merch')
and
@.strBrand ('111,222')
Arnie Rowland escreveu:
> Table DDL would help in diagnosis.
> If service.code is a numeric datatype then, @.strService ('111,222') would=
be
> correct. If service.code is a character datatype then each comma separated
> element needs quotes around it.
> Please verify the datatype for service.code.
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> "Pedro" <p_costa@.sapo.pt> wrote in message
> news:1151083291.840423.149320@.r2g2000cwb.googlegroups.com...
> I ment to write @.strService ('111,222') and not
> @.strService ('111,'222').
> And I don't think the problem is the single quotes around each element
> because my query string in VB.net is:
>
> strQuery =3D "EXECUTE " & database & ".dbo.spGetInvoiceData '" & client &
> "', '" & brandCode & "', '" & serviceCode & "', '" & startDate & "', '"
> & endDate & "'"
>
>
> Arnie Rowland escreveu:
ers
ues
me
=3D1|||IN() list in SQL cannot accept a parameter. Either for scalar values use the
equality operator ( = ) or for multiple values use an appropriate technique
detailed at:
http://www.sommarskog.se/arrays-in-sql.html
Anith|||The way I see it, @.strBrand has a length problem.
It's defined as a varchar(10), and with @.strService('dsmt','merch'), you are
trying to push 14 characters into it.
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Pedro" <p_costa@.sapo.pt> wrote in message
news:1151085462.464011.201720@.c74g2000cwc.googlegroups.com...
Sorry, but I' ve supllied wrong information:
@.strService('dsmt','merch')
and
@.strBrand ('111,222')
Arnie Rowland escreveu:
> Table DDL would help in diagnosis.
> If service.code is a numeric datatype then, @.strService ('111,222') would
> be
> correct. If service.code is a character datatype then each comma separated
> element needs quotes around it.
> Please verify the datatype for service.code.
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> "Pedro" <p_costa@.sapo.pt> wrote in message
> news:1151083291.840423.149320@.r2g2000cwb.googlegroups.com...
> I ment to write @.strService ('111,222') and not
> @.strService ('111,'222').
> And I don't think the problem is the single quotes around each element
> because my query string in VB.net is:
>
> strQuery = "EXECUTE " & database & ".dbo.spGetInvoiceData '" & client &
> "', '" & brandCode & "', '" & serviceCode & "', '" & startDate & "', '"
> & endDate & "'"
>
>
> Arnie Rowland escreveu:|||Your problem is, quite simply, that you're using IN wrong. This is what
you're telling SQL Server when you pass it a @.strService value of
('dsmt,merch'):
@.strService IN ('dsmt,merch')
What you're trying to achieve is this:
@.strService IN ('dsmt', 'merch')
The way to do this is to break up your comma-delimited string into a temp
table or table variable and join/subquery on it. Here's one method:
http://groups.google.com/group/micr...de=source&hl=en
Here's more methods: http://www.sommarskog.se/arrays-in-sql.html
"Pedro" <p_costa@.sapo.pt> wrote in message
news:1151080483.044837.122720@.y41g2000cwy.googlegroups.com...
I have a problem in a stored proc where I pass more than one value to
the parameters inside a IN clause: it doesnt retrieve any data. But
if there's only one value I get results.
here's the code:
CREATE PROCEDURE spGetInvoiceData
@.strClient varchar(10),
@.strBrand varchar(10),
@.strService varchar(10),
@.dtBeguinDate datetime,
@.dtEndDate datetime,
@.strCamp varchar(10) = NULL
AS
select request.orgunit as unit, campaign.name as campaign, service.name
as service, entity.name as destinationEntity, requestitem.request,
request.efectivedate, item.itemcode, item.name as item, brand.name as
brand, requestitem.amount,
item.weigth, facturationtype.name as facturacao,
facturationstep.value, facturationstep.lowerbound,
facturationstep.upperbound, facturationstep.addvalue
into #ttemp
from request, requestitem, item, brand, service, entity,
facturationtype, facturationstep, campaign
where requestitem.request in (select code from request where state=1
and (requeststate='expd' or requeststate='done' or
requeststate='closed' or requeststate='atrib')
and client like @.strClient and efectivedate >= @.dtBeguinDate and
efectivedate <= @.dtEndDate) and requestitem.state=1 and
(facturationtype.code = request.invoicetype
and request.destinationentity = entity.code
and request.service = service.code
and request.code=requestitem.request
and item.code = requestitem.item
and requestitem.brand = brand.code
and facturationtype.code = facturationstep.facturationtypecode
and request.campaign=campaign.code)
and item.state=1 and requestitem.state=1 and request.state=1 and
service.state=1 and brand.state=1 and campaign.state=1
and entity.state=1 and facturationtype.state=1 and
request.toinvoice=1 and facturationstep.state = 1
and service.code in (@.strService) and brand.code in (@.strBrand)
and (request.campaign = @.strCamp or @.strCamp IS NULL)
order by request.efectivedate, REQUESTITEM.REQUEST,BRAND.NAME
select *,
(select distinct sum(t1.weigth * t1.amount) from #ttemp t1 where
t1.request = t2.request and t1.brand = t2.brand) as pesomarca,
(select distinct sum(weigth * amount) from #ttemp t1 where t1.request
= t2.request) as pesopedido
from #ttemp t2
drop table #ttemp
GO
Can anyone help?sql

Param to SP, adDecimal

I am passing a parameter to a stored proce that will have the value of a
percent, like .068
Here is where I set it up:
Set prmPercent = .CreateParameter("Percent", adDecimal, adParamInput, , 0.5)
in the stored proc, I have
@.Percent dec(18,9),
When I run it, I get "Percision is Invalid" when it calls the proc. I can't
find much on it in Book on-line, or from searching this forum.
Do I have to set the @.Percent in the stored proc up differently?
Thanks,
SteveYou have to set precision and scale properties also.
Set prmPercent.Precision = 18
Set prmPercent.NumericScale = 9
AMB
"SteveInBeloit" wrote:

> I am passing a parameter to a stored proce that will have the value of a
> percent, like .068
> Here is where I set it up:
> Set prmPercent = .CreateParameter("Percent", adDecimal, adParamInput, , 0.
5)
> in the stored proc, I have
> @.Percent dec(18,9),
> When I run it, I get "Percision is Invalid" when it calls the proc. I can
't
> find much on it in Book on-line, or from searching this forum.
> Do I have to set the @.Percent in the stored proc up differently?
> Thanks,
> Steve|||I can't get that to compile, I used
Set prmPercent = .CreateParameter("Percent", adDecimal,
adParamInput, , 0.5)
Set prmPercent.Precision = 18
Set prmPercent.NumericScale = 9
I get "Invalid use of Properties" and it highlights the Set
prmPercent.Precision = 18 statement.
Any idea?
"Alejandro Mesa" wrote:
> You have to set precision and scale properties also.
> Set prmPercent.Precision = 18
> Set prmPercent.NumericScale = 9
>
> AMB
> "SteveInBeloit" wrote:
>|||Sorry, try:
with prmPercent
.Precision = 18
.NumericScale = 9
end with
AMB
"SteveInBeloit" wrote:
> I can't get that to compile, I used
> Set prmPercent = .CreateParameter("Percent", adDecimal,
> adParamInput, , 0.5)
> Set prmPercent.Precision = 18
> Set prmPercent.NumericScale = 9
> I get "Invalid use of Properties" and it highlights the Set
> prmPercent.Precision = 18 statement.
> Any idea?
>
> "Alejandro Mesa" wrote:
>|||AMB
THANK YOU VERY MUCH! That did it. I struggled with this quite some time
today.
Thanks again,
Steve
"Alejandro Mesa" wrote:
> Sorry, try:
> with prmPercent
> .Precision = 18
> .NumericScale = 9
> end with
>
> AMB
> "SteveInBeloit" wrote:
>

Parallelism and performance

Hi,
We have a stored procedure that runs slow when the execution plan uses
parallelism vs. if does not.
My Question is when is it helpful to have parallelism and when not. Also is
it advisable to configure the sql server to not use parallelism at all.
Thanks
RahulSometimes SQL Server just creates parallel plans which do not perform well, although this seems to
be less and less of an issue with each release (and service pack). You can disable parallelism by:
MAXDOP 1 optimizer hint in the query
sp_configure and set "max degree of parallelism" to 1, will affect all queries
The "cost threshold for parallelism" affects the threshold for when parallel plans are considered
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rahul" <reach_aggarwal@.hotmail.com> wrote in message news:e0QcAwyOHHA.1248@.TK2MSFTNGP02.phx.gbl...
> Hi,
> We have a stored procedure that runs slow when the execution plan uses parallelism vs. if does
> not.
> My Question is when is it helpful to have parallelism and when not. Also is it advisable to
> configure the sql server to not use parallelism at all.
> Thanks
> Rahul
>|||On Thu, 18 Jan 2007 19:18:26 +0100, "Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:
>Sometimes SQL Server just creates parallel plans which do not perform well, although this seems to
>be less and less of an issue with each release (and service pack). You can disable parallelism by:
>MAXDOP 1 optimizer hint in the query
>sp_configure and set "max degree of parallelism" to 1, will affect all queries
>The "cost threshold for parallelism" affects the threshold for when parallel plans are considered
Hey Tibor, I had 21 copies of my SPID yesterday on a select query, on
a box with four processors.
I think that set a new personal best for me!
J.|||Sounds like a query that needs optimization:).
--
Andrew J. Kelly SQL MVP
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:ovi0r2tt33p1do0ialubbtbn5ujlfua75a@.4ax.com...
> On Thu, 18 Jan 2007 19:18:26 +0100, "Tibor Karaszi"
> <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:
>>Sometimes SQL Server just creates parallel plans which do not perform
>>well, although this seems to
>>be less and less of an issue with each release (and service pack). You can
>>disable parallelism by:
>>MAXDOP 1 optimizer hint in the query
>>sp_configure and set "max degree of parallelism" to 1, will affect all
>>queries
>>The "cost threshold for parallelism" affects the threshold for when
>>parallel plans are considered
> Hey Tibor, I had 21 copies of my SPID yesterday on a select query, on
> a box with four processors.
> I think that set a new personal best for me!
> J.
>|||On Fri, 19 Jan 2007 09:50:24 -0500, "Andrew J. Kelly"
<sqlmvpnooospam@.shadhawk.com> wrote:
>Sounds like a query that needs optimization:).
There must have been at least 21 rows to check!
J.sql

Parallelism and performance

Hi,
We have a stored procedure that runs slow when the execution plan uses
parallelism vs. if does not.
My Question is when is it helpful to have parallelism and when not. Also is
it advisable to configure the sql server to not use parallelism at all.
Thanks
Rahul
On Thu, 18 Jan 2007 19:18:26 +0100, "Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:

>Sometimes SQL Server just creates parallel plans which do not perform well, although this seems to
>be less and less of an issue with each release (and service pack). You can disable parallelism by:
>MAXDOP 1 optimizer hint in the query
>sp_configure and set "max degree of parallelism" to 1, will affect all queries
>The "cost threshold for parallelism" affects the threshold for when parallel plans are considered
Hey Tibor, I had 21 copies of my SPID yesterday on a select query, on
a box with four processors.
I think that set a new personal best for me!
J.
|||Sounds like a query that needs optimization.
Andrew J. Kelly SQL MVP
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:ovi0r2tt33p1do0ialubbtbn5ujlfua75a@.4ax.com...
> On Thu, 18 Jan 2007 19:18:26 +0100, "Tibor Karaszi"
> <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:
>
> Hey Tibor, I had 21 copies of my SPID yesterday on a select query, on
> a box with four processors.
> I think that set a new personal best for me!
> J.
>
|||On Fri, 19 Jan 2007 09:50:24 -0500, "Andrew J. Kelly"
<sqlmvpnooospam@.shadhawk.com> wrote:

>Sounds like a query that needs optimization.
There must have been at least 21 rows to check!
J.

Parallelism and performance

Hi,
We have a stored procedure that runs slow when the execution plan uses
parallelism vs. if does not.
My Question is when is it helpful to have parallelism and when not. Also is
it advisable to configure the sql server to not use parallelism at all.
Thanks
RahulSometimes SQL Server just creates parallel plans which do not perform well,
although this seems to
be less and less of an issue with each release (and service pack). You can d
isable parallelism by:
MAXDOP 1 optimizer hint in the query
sp_configure and set "max degree of parallelism" to 1, will affect all queri
es
The "cost threshold for parallelism" affects the threshold for when parallel
plans are considered
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rahul" <reach_aggarwal@.hotmail.com> wrote in message news:e0QcAwyOHHA.1248@.TK2MSFTNGP02.phx
.gbl...
> Hi,
> We have a stored procedure that runs slow when the execution plan uses par
allelism vs. if does
> not.
> My Question is when is it helpful to have parallelism and when not. Also i
s it advisable to
> configure the sql server to not use parallelism at all.
> Thanks
> Rahul
>|||On Thu, 18 Jan 2007 19:18:26 +0100, "Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:

>Sometimes SQL Server just creates parallel plans which do not perform well,
although this seems to
>be less and less of an issue with each release (and service pack). You can
disable parallelism by:
>MAXDOP 1 optimizer hint in the query
>sp_configure and set "max degree of parallelism" to 1, will affect all quer
ies
>The "cost threshold for parallelism" affects the threshold for when parallel plans
are considered
Hey Tibor, I had 21 copies of my SPID yesterday on a select query, on
a box with four processors.
I think that set a new personal best for me!
J.|||Sounds like a query that needs optimization.
Andrew J. Kelly SQL MVP
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:ovi0r2tt33p1do0ialubbtbn5ujlfua75a@.
4ax.com...
> On Thu, 18 Jan 2007 19:18:26 +0100, "Tibor Karaszi"
> <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:
>
> Hey Tibor, I had 21 copies of my SPID yesterday on a select query, on
> a box with four processors.
> I think that set a new personal best for me!
> J.
>|||On Fri, 19 Jan 2007 09:50:24 -0500, "Andrew J. Kelly"
<sqlmvpnooospam@.shadhawk.com> wrote:

>Sounds like a query that needs optimization.
There must have been at least 21 rows to check!
J.

Parallel execution in SQL Server

Is there any way to run a stored procedure in parallel to another one? i.e. I have a stored procedure that sends an email. I then scan a table and send any unsent emails. I do not want the second part to slow the response to the user.

Why don't make a JOB for send any unsend emails ?

Store procedure only insert a row in a buffer table and job consume the rows in that table.

|||I have tried using a Job before and it was not a nice experience - guess I will have to sort it out properly this time.

Painfully slow scripting

Using SqlExpress 2005, I have a database with over 1000 stored procedures
for a project still in development. From time-to-time I need to script out
the stored procedures for backup or transfer to another system. For the past
year, this has been a relatively quick scripting process, but lately it has
become excrutiatingly slow. There are no issues with the OS or the hardware
and all other software runs normally. Firewall is on, virus protection and
scan is up-to-date and no goofy pop-up issues nor any apparent malicious
software. Any thoughts on this issue would be appreciated.I think that Erland (I believe it was) had some improvements by setting forced parameterization (see
ALTER DATABASE).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Earl" <earl@.nospam.com> wrote in message news:uZLfdu5%23HHA.4200@.TK2MSFTNGP04.phx.gbl...
> Using SqlExpress 2005, I have a database with over 1000 stored procedures for a project still in
> development. From time-to-time I need to script out the stored procedures for backup or transfer
> to another system. For the past year, this has been a relatively quick scripting process, but
> lately it has become excrutiatingly slow. There are no issues with the OS or the hardware and all
> other software runs normally. Firewall is on, virus protection and scan is up-to-date and no goofy
> pop-up issues nor any apparent malicious software. Any thoughts on this issue would be
> appreciated.
>|||Earl (earl@.nospam.com) writes:
> Using SqlExpress 2005, I have a database with over 1000 stored
> procedures for a project still in development. From time-to-time I need
> to script out the stored procedures for backup or transfer to another
> system. For the past year, this has been a relatively quick scripting
> process, but lately it has become excrutiatingly slow. There are no
> issues with the OS or the hardware and all other software runs normally.
> Firewall is on, virus protection and scan is up-to-date and no goofy
> pop-up issues nor any apparent malicious software. Any thoughts on this
> issue would be appreciated.
Yes, as Tibor set, you can gain speed by saying
ALTER DATABASE db SET PARAMETERIZATION FORCED
this setting causes SQL Server to parameterize every query sent to it.
That is, a query like:
SELECT * FROM Orders WHERE OrderStatus = 'Bad' AND OrderDate < '20070101'
will be put into the cache as:
SELECT * FROM Orders WHERE OrderStatus = @.1 AND OrderDate < @.2
This measure can be a big speed winner with an application that generates
SQL with inlined parameter values, despite this being bad practice. And Mmgt
Studio is such an application. It's certainly embarrassing that Microsoft
themselves cannot adhere to good practice.
The reason this is a winner is that when the same query is repeated all
over again, just with different parameter values, SQL Server does not have
to spend time on compiling the queries.
But you should not have forced parameterization with a well-written
application that uses parameterised queries or stored procedures, since
SQL my parameterise where it shouldn't.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Paging with Stored Procedures

I have been browsing the newsgroups trying to find a good solution for this
problem.
I have a resultset and I need to show that information in pages. I also need
to have this sorted by a specific column (Date for example)
I found the following solution written by Don Arsenault that works very
well:
/*
The above routines assume that the resultset is ordered by the unique key.
If that's not true, a combination of a sort column and the unique key
can be used. Pass the sort column value as well as the unique key to the
next_page and previous_page procedures. Make sure the table has an index
on the combination of the sort column and the unqiue key.
*/
CREATE PROCEDURE next_page
@.current_page_last_row_key int,
@.current_page_last_row_sort int
AS
--return first page if parameters are null.
IF (@.current_page_last_row_key is null)
SELECT TOP 10 *
FROM my_big_table
ORDER BY sort_column, unique_key
ELSE
SELECT TOP 10 *
FROM my_big_table
WHERE
(sort_column >= @.current_page_last_row_sort)
and (
(sort_column > @.current_page_last_row_sort)
or (unique_key > @.current_page_last_row_key)
)
ORDER BY sort_column, unique_key
CREATE PROCEDURE previous_page
@.current_page_first_row_key int,
@.current_page_first_row_sort int
AS
--return last page if parameters are null.
IF (@.current_page_first_row_sort_key is null)
SELECT *
FROM
(
SELECT TOP 10 *
FROM my_big_table
ORDER BY sort_column DESC, unique_key DESC
) AS Reorder
ORDER BY unique_key
ELSE
SELECT *
FROM
(
SELECT TOP 10 *
FROM my_big_table
WHERE
(sort_column <= @.current_page_last_row_sort)
and (
(sort_column < @.current_page_last_row_sort)
or (unique_key < @.current_page_last_row_key)
)
ORDER BY sort_column DESC, unique_key DESC
) AS Reorder
ORDER BY sort_column, unique_key
That works great when you want to move from one page to the next (or to the
previous one), but I don't know
how I can go to a specific page. For example, go to page 100.
Do you guys know how I can get this?
ThanksYou may also want to refer to www.aspfaq.com/2120 for some ideas.
Anith

Paging query without using stored procedure

Hello, my table is clustered according to the date. Has anyone found an efficient way to page through 16 million rows of data? The query that I have takes waaaay too long. It is really fast when I page through information at the beginning but when someone tries to access the 9,000th page sometimes it has a timeout error. I am using sql server 2005 let me know if you have any ideas. Thanks

I am also thinking about switch datavase software to something that can handle that many rows. Let me know if you have a suggestion on a particular software that can handle paging through 16 million rows of data.

Hi~

You may take a look at this:

Efficiently Paging Through Large Amounts of Data (VB)

Efficiently Paging Through Large Amounts of Data(C#)

Hope this helps.