Need help constructing stored procedure

Hi,

I need to construct a stored procedure that will accept a set of comma seperated numbers. What I would like to do is something like this

create procedure shopping_cart(@.contents as varchar) as
select dvd_title
from movie_dvd
where dvd_detail_id in (@.contents)

dvd_detail_id is defined as int in the table.
The problem is if I declare @.contents as varchar, the procedure only recognizes the first number and ignores the others. Does anyone know how to get around this?

Thanks

Dan@.array is your @.contents...you need to pass the separator too..it can be a ',' or '-' etc

Create procedure shopping_cart (@.Array varchar(1000), @.separator char(1) ) AS
set nocount on
-- @.Array is the array we wish to parse
-- @.Separator is the separator charactor such as a comma
declare @.separator_position int -- This is used to locate each separator character
declare @.array_value varchar(1000) -- this holds each array value as it is returned
-- For my loop to work I need an extra separator at the end. I always look to the
-- left of the separator character for each array value
set @.array = @.array + @.separator
-- Loop through the string searching for separtor characters
while patindex('%' + @.separator + '%' , @.array) <> 0
begin
 -- patindex matches the a pattern against a string
 select @.separator_position = patindex('%' + @.separator + '%' , @.array)
 select @.array_value = left(@.array, @.separator_position - 1)
 -- This is where you process the values passed.
 -- Replace this select statement with your processing
 -- @.array_value holds the value of this element of the array
 select Array_Value = @.array_value
 -- This replaces what we just processed with and empty string
 select @.array = stuff(@.array, 1, @.separator_position, '')
end
set nocount off
GO

hth|||I did the following on some other project

create procedure shopping_cart(@.contents as varchar) as
select dvd_title
from movie_dvd
where CharIndex(',' + Cast(dvd_detail_id as varchar(3)) + ',', @.contents) > 0

In the above case you should be sure that the values passed in are going to have , next to it. For eample, it should be ,1,2,3,

Does the above make sense?

AP

Need help comparing output

Hi everyone. I am having difficulty with the following stored procedure:
CREATE procedure A_sp_UpdatePricing_R_parts_test as
/* Declare variables */
Begin
Declare @.SellingPrice decimal(9),
@.StockCode varchar(30)
/* Declare cursor and open for processing */
Declare System_Cursor Cursor for Select StockCode from InvMaster where StockCode in ('03-18320-00')
Open System_Cursor
/* Fetch value into cursor */
Fetch Next from System_Cursor into @.StockCode
While @.@.Fetch_Status = 0
Begin
Set @.SellingPrice = Null
Select @.SellingPrice = SellingPrice from InvPrice where StockCode = @.StockCode
If @.SellingPrice >0
Update InvPrice set SellingPrice = 222
Where (StockCode = @.StockCode and StockCode like '%R')
Fetch Next from System_Cursor into @.StockCode
End
/* Close cursor */
Close System_Cursor
Deallocate System_Cursor
End
GO
What it should do is we have part numbers that end with -R or R for refurbished and most of these parts have regular part numbers that don't end in R. I am supposed to get the price for the regular one, discount it by 15% and use that to set the price for the refurbished part.
I don't know how to set the price i get for the part that is equal to the part in question but has an R in the end, which stands for refurbished.
Any help would be much appreciated.This is a multi-part message in MIME format.
--=_NextPart_000_0028_01C3C8D7.32408980
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: 7bit
You don't need a cursor for this. Try:
update InvPrice
set
SellingPrice = (select SellingPrice
from InvPrice
where StockCode = '03-18320-00') * 0.85
where
StockCode like '03-18320-00%R'
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
.
"Andrei" <anonymous@.discussions.microsoft.com> wrote in message
news:42E31D22-31B6-4BE5-AEBF-5DC5B34BD0A9@.microsoft.com...
Hi everyone. I am having difficulty with the following stored procedure:
CREATE procedure A_sp_UpdatePricing_R_parts_test as
/* Declare variables */
Begin
Declare @.SellingPrice decimal(9),
@.StockCode varchar(30)
/* Declare cursor and open for processing */
Declare System_Cursor Cursor for Select StockCode from InvMaster where
StockCode in ('03-18320-00')
Open System_Cursor
/* Fetch value into cursor */
Fetch Next from System_Cursor into @.StockCode
While @.@.Fetch_Status = 0
Begin
Set @.SellingPrice = Null
Select @.SellingPrice = SellingPrice from InvPrice where StockCode =@.StockCode
If @.SellingPrice >0
Update InvPrice set SellingPrice = 222
Where (StockCode = @.StockCode and StockCode
like '%R')
Fetch Next from System_Cursor into @.StockCode
End
/* Close cursor */
Close System_Cursor
Deallocate System_Cursor
End
GO
What it should do is we have part numbers that end with -R or R for
refurbished and most of these parts have regular part numbers that don't end
in R. I am supposed to get the price for the regular one, discount it by 15%
and use that to set the price for the refurbished part.
I don't know how to set the price i get for the part that is equal to the
part in question but has an R in the end, which stands for refurbished.
Any help would be much appreciated.
--=_NextPart_000_0028_01C3C8D7.32408980
Content-Type: text/html;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
=EF=BB=BF<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

You don't need a cursor for =this. Try:
update InvPrice
set
SellingPrice ==3D (select SellingPrice
= from InvPrice
= where StockCode =3D '03-18320-00') * 0.85
where
=StockCode like '03-18320-00%R'
-- =Tom
----Thomas A. =Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql.
"Andrei" wrote in message news:42E=31D22-31B6-4BE5-AEBF-5DC5B34BD0A9@.microsoft.com...Hi everyone. I am having difficulty with the following stored procedure:CREATE procedure A_sp_UpdatePricing_R_parts_test as/* Declare variables */BeginDeclare @.SellingPrice =decimal(9),@.StockCode varchar(30) /* Declare cursor and open for processing */Declare System_Cursor Cursor for Select StockCode from =InvMaster where StockCode in ('03-18320-00')Open System_Cursor/* Fetch =value into cursor */Fetch Next from System_Cursor into =@.StockCodeWhile @.@.Fetch_Status =3D 0BeginSet @.SellingPrice =3D NullSelect @.SellingPrice =3D SellingPrice from =InvPrice where StockCode =3D @.StockCodeIf @.SellingPrice >0  =; = Update InvPrice set SellingPrice =3D 222 &nbs=p;  =; = &nb=sp; &nbs=p; Where (StockCode =3D @.StockCode and StockCode like '%R') Fetch Next from System_Cursor into @.StockCodeEnd/* Close cursor */Close System_CursorDeallocate System_CursorEndGOWhat =it should do is we have part numbers that end with -R or R for refurbished and =most of these parts have regular part numbers that don't end in R. I am supposed =to get the price for the regular one, discount it by 15% and use that to set =the price for the refurbished part.I don't know how to set the price i get =for the part that is equal to the part in question but has an R in the end, =which stands for refurbished. Any help would be much =appreciated.

---

--=_NextPart_000_0028_01C3C8D7.32408980--

need help- case...when not working

I am getting the error
Incorrect syntax near 'fValue1_precision'.
from the stored procedure below
Can someone see what is wrong?
Thanks
...
@.MyField3 float(8)=NULL,
@.MyField3_precision int=NULL,
...
SELECT
MyTable.[ID],
MyTable.MyField1,
MyTable.MyField2,
(CASE
WHEN MyField3_precision IS NULL THEN MyField3
ELSE CONVERT(decimal(10, MyField3_precision), MyField3)
END) AS MyField3,
MyTable.MyField4,
MyTable.MyField5,Mad Scientist Jr wrote:
> I am getting the error
> Incorrect syntax near 'fValue1_precision'.
> from the stored procedure below
> Can someone see what is wrong?
> Thanks
> ...
> @.MyField3 float(8)=NULL,
> @.MyField3_precision int=NULL,
> ...
> SELECT
> MyTable.[ID],
> MyTable.MyField1,
> MyTable.MyField2,
> (CASE
> WHEN MyField3_precision IS NULL THEN MyField3
> ELSE CONVERT(decimal(10, MyField3_precision), MyField3)
> END) AS MyField3,
> MyTable.MyField4,
> MyTable.MyField5,
Given that the code fragment you posted doesn't include the name
referred to in the error message it's kind of hard to be sure...
However, this bit isn't valid syntax:
CONVERT(decimal(10, MyField3_precision)
You can't use a column value for the precision. The precision must be a
literal value.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||the actual error was
Incorrect syntax near 'MyField3_precision'.
but that sounds like it could be the problem
thanks|||so the correct way to do it would be something like this?
(CASE
WHEN MyField3_precision IS NULL THEN MyField3
WHEN MyField3_precision=0 THEN CONVERT(decimal(10, 0),
MyField3)
--WHEN MyField3_precision=0 THEN CONVERT(int, MyField3)
WHEN MyField3_precision=1 THEN CONVERT(decimal(10, 1),
MyField3)
WHEN MyField3_precision=2 THEN CONVERT(decimal(10, 2),
MyField3)
WHEN MyField3_precision=3 THEN CONVERT(decimal(10, 3),
MyField3)
WHEN MyField3_precision=4 THEN CONVERT(decimal(10, 4),
MyField3)
WHEN MyField3_precision=5 THEN CONVERT(decimal(10, 5),
MyField3)
WHEN MyField3_precision=6 THEN CONVERT(decimal(10, 6),
MyField3)
WHEN MyField3_precision=7 THEN CONVERT(decimal(10, 7),
MyField3)
WHEN MyField3_precision=8 THEN CONVERT(decimal(10, 8),
MyField3)
WHEN MyField3_precision=9 THEN CONVERT(decimal(10, 9),
MyField3)
WHEN MyField3_precision=10 THEN CONVERT(decimal(10, 10),
MyField3)
ELSE MyField3
END) AS MyField3,|||Mad Scientist Jr wrote:
> so the correct way to do it would be something like this?
> (CASE
> WHEN MyField3_precision IS NULL THEN MyField3
> WHEN MyField3_precision=0 THEN CONVERT(decimal(10, 0),
> MyField3)
> --WHEN MyField3_precision=0 THEN CONVERT(int, MyField3)
> WHEN MyField3_precision=1 THEN CONVERT(decimal(10, 1),
> MyField3)
> WHEN MyField3_precision=2 THEN CONVERT(decimal(10, 2),
> MyField3)
> WHEN MyField3_precision=3 THEN CONVERT(decimal(10, 3),
> MyField3)
> WHEN MyField3_precision=4 THEN CONVERT(decimal(10, 4),
> MyField3)
> WHEN MyField3_precision=5 THEN CONVERT(decimal(10, 5),
> MyField3)
> WHEN MyField3_precision=6 THEN CONVERT(decimal(10, 6),
> MyField3)
> WHEN MyField3_precision=7 THEN CONVERT(decimal(10, 7),
> MyField3)
> WHEN MyField3_precision=8 THEN CONVERT(decimal(10, 8),
> MyField3)
> WHEN MyField3_precision=9 THEN CONVERT(decimal(10, 9),
> MyField3)
> WHEN MyField3_precision=10 THEN CONVERT(decimal(10, 10),
> MyField3)
> ELSE MyField3
> END) AS MyField3,
No. A column can only have one type, scale and precision and that's
determined at compile-time, so the multiple versions of CONVERT aren't
doing much for you. If you want each row formatted differently in SQL
then you'll have to convert the values to strings. Probably makes more
sense to return numbers at full precision and then format them in the
client app or presentation tier.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Strange, the above code is working. Using case-when I am getting back
the precision I want in the select (the result is being displayed in a
datagrid). It appears to be working, so what is wrong with the code?
Thanks|||Mad Scientist Jr wrote:
> Strange, the above code is working. Using case-when I am getting back
> the precision I want in the select (the result is being displayed in a
> datagrid). It appears to be working, so what is wrong with the code?
> Thanks
Depends what you mean by "appears to be working". What is it supposed
to do? Formatting of numbers is controlled by your client application,
not by SQL Server. Possibly the implict rounding performed by CONVERT
is influencing the way your app displays the numbers - but you don't
need CONVERT to do rounding. You can use ROUND or FLOOR instead.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Back to basics. CASE is an expression. An expression returns a scalar
result of one and only one data type. Why? Because a column can have
one and only one data type!! You missed the very definition of 1NF.
You might also way to learn HUGE differences between columns and
fields. Fields can change types and structure because in a file
system, the host program gives meanng to the data as it reads it from
left to right. In a RDBMS, the meaning is in the schema, not the
applications.

need help adding onto my stored procedure

calculate age:
CREATE PROCEDURE selectage
AS
SELECTAge = datediff(yy, birthday, getdate()) +
case when datepart(dy, birthday) <= datepart(dy, getdate()) then 0
else -1
end
FROM nopay
my ? is how do fix this problem:
I add in
WHERE (((Age)>= @.age1 And (Age)<= @.age2))
and it will complain about Age is not a column, so how would i store the underlined and bold Age aboves value?

Unfortunately SQL won't handle it that way. One approach is to use your calculation in the WHERE column:

WHERE

    datediff(yy, birthday, getdate()) +

        case

            when datepart(dy, birthday) <= datepart(dy, getdate()) then 0

        else

             -1

        end BETWEEN @.age1 And @.age2)

Alternately, you could do this:

SELECT

    age

FROM

(SELECT

   Age = datediff(yy, birthday, getdate()) +

        case

            when datepart(dy, birthday) <= datepart(dy, getdate()) then 0

        else

            -1

        end

FROM

    nopay) AS subquery

WHERE Age BETWEEN @.age1 And @.age2

Also, I would suggest writing a user-defined function (UDF) tocalculate the age. This will keep your code cleaner and is thesort of thing you will likely need to reuse. For example:

SELECT

   dbo.fnAge(birthday) AS age

FROM

    nopay

WHERE

     dbo.fnAge(birthday)BETWEEN @.age1 And @.age2)

CREATE FUNCTION age (@.indate datetime) -- note: untested!!

RETURNS int

AS

BEGIN

    DECLARE @.age int

    SELECT @.age = datediff(yy, @.indate, getdate()) +

        CASE

            WHEN datepart(dy, @.indate) <= datepart(dy, getdate()) then 0

        ELSE

            -1

        END

    RETURN @.age

END

|||

Can I kiss you?

One more thing I tried to add onto the where and it gave me an error about the comma

WHERE Age BETWEEN @.age1 And @.age2, breed = @.breed

i also did

WHERE breed = @.breed AND Age BETWEEN @.age1 And @.age2

than it complained about input was not in correct form

Dim paramBreed As SqlClient.SqlParameter
paramBreed = New SqlClient.SqlParameter("@.breed", SqlDbType.NVarChar, breed.SelectedValue)
paramBreed.Value = breed.SelectedValue
objCM.Parameters.Add(paramBreed)

CREATE PROCEDURE selectage
@.age1 int,
@.age2 int,
@.breed nvarchar
AS
SELECT
age, username, breed

FROM
(SELECT username, breed,
Age = datediff(yy, birthday, getdate()) +
case
when datepart(dy, birthday) <= datepart(dy, getdate()) then 0
else
-1
end
FROM
nopay) AS subquery
WHERE breed = @.breed AND Age BETWEEN @.age1 And @.age2

InvalidCastException: Cast from string "Dalmation" to type 'Integer' is not valid.]
if i change that statement to

paramBreed = New SqlClient.SqlParameter("@.breed", SqlDbType.NVarChar)

the program will execute but than it wont display as if the breed = @.breed was incorrect

|||Don't get excited, it's only SQL after all!
If that's an exact copy-and-paste of your WHERE clause, you need a space between the @.breed and the AND:
WHERE breed = @.breed AND Age BETWEEN @.age1 And @.age2
(You should use the UDF. This is the sort of thing they were made for.)
|||

my mistake, no the syntax checks fine, just the asp gives that error

i tried writing

SELECT

   dbo.fnAge(birthday) AS age

FROM

and i dont understand dbo.fnAge

do i delete create stored procedure?

|||In your stored procedure, give a length to the @.breed nvarchar parameter
@.breed nvarchar(100)
And in your code, try this line instead for creating the parameter:
paramBreed = New SqlClient.SqlParameter("@.breed", SqlDbType.NVarChar, 100)
(Be sure to use the same length that corresponds to your database column.)
|||

Big thanks,

CREATE PROCEDURE selectage
@.age1 int,
@.age2 int,
@.breed nvarchar
AS
SELECT
age, username, breed

FROM
(SELECT username, breed,
Age = datediff(yy, birthday, getdate()) +
case
when datepart(dy, birthday) <= datepart(dy, getdate()) then 0
else
-1
end
FROM
nopay WHEREfirst.username = second.username) AS subquery
WHERE breed = @.breed AND Age BETWEEN @.age1 And @.age2

i figured out how to whatever you call it when u have same feilds and eliminate unwanted data

sql

NEED HELP ABOUT CURSOR

Hello,
I have this Store Procedure and i am new in "Cursor" programming
The store Procedure is this
CREATE PROCEDURE Testing_Cursor AS
DECLARE @.AccCode varchar(12)
DECLARE @.YEAR varchar(4)
Declare CODE CURSOR
FOR
SELECT DISTINCT(NLAcc_Code)
FROM NLMovement_TMP
OPEN CODE
BEGIN
FETCH NEXT FROM CODE INTO @.AccCode
WHILE @.@.FETCH_STATUS = 0
BEGIN
DECLARE CYEAR CURSOR
FOR SELECT DISTINCT(AccYear) FROM NLMOVEMENT_TMP WHERE
NLACC_CODE=@.ACCCODE
OPEN CYEAR
FETCH NEXT FROM CYEAR INTO @.YEAR
WHILE @.@.FETCH_STATUS = 0
BEGIN
SELECT SUM(MOVEMENT)
FROM NLMOVEMENT_TMP
WHERE NLACC_CODE=@.ACCCODE
AND ACCYEAR=@.YEAR
END
CLOSE CYEAR
END
END
CLOSE CODE
GO
I have an endless lopp in the second WHILE @.@.FETCH_STATUS = 0
it avtually never goes into the begin - end code after .
Am i using the WHILE statment correct'
Can anyone find the what is wrong with it'
Thanks
SavvasThe first thing you should learn about cursors is that you don't need
them! At least 99.99%, of the time cursors are not a good solution to
problems in SQL. Try this instead
SELECT SUM(movement)
FROM nlmovement_tmp
GROUP BY nlacc_code, accyear ;
If that's not what you wanted then please post DDL and sample data so
that we can understand your requireemnts better.
I would suggest you don't bother learning about cursors until you a
much more expert with "proper" SQL. Only then will you have the
knowledge to judge the exceptional situations when cursors make sense.
Hope this helps.
David Portas
SQL Server MVP
--|||Hi Sawas,
AND ACCYEAR=3D@.YEAR
FETCH NEXT FROM CODE INTO @.AccCode --missed that here
END
CLOSE CYEAR
END
END
CLOSE CODE
GO
But what about NOT using a cursor, they can be slow (not to say that
they are slow, because I don=B4t want to start a religious war in here),
but If you are onyl doing queries, you should rely on a non cursor
solution:
SELECT SUM(MOVEMENT)
FROM NLMOVEMENT_TMP
GROUP BY NLACC_CODE,ACCYEAR
HTH, jens Suessmeyer.|||Well Actually I have this situation
I have a table with
AccCode,
AccMovement,
AccYear, and
AccPeriod
and my data looks like this
AccCode AccYear AccPeriod sumaryOfThe movement
110401 1998 02 541870.00
110401 1998 03 1210000.00
110401 1998 04 2687330.00
110401 1998 05 1220450.00
110401 1998 06 3508000.00
110401 1998 07 5606620.00
110401 1998 08 1567080.00
110401 1998 09 150000.00
110401 1998 10 1202180.00
110401 1998 11 1373980.00
110401 1998 12 2604600.00
And this account has data for many years ,
Now i want to make a table like this
accCode , AccYear , AccPeriod, Opening balance , movement ,
OpeningBalance+Movement where opening balance = closing balnce of
previus period
how can i do this ?|||This would be a bit easier if you used proper dates. Put your financial
periods in a Calendar table instead.
I'm just guessing at the following table structure because you didn't
post it. Note the key.
CREATE TABLE nlmovement (acccode INTEGER NOT NULL, accyear INTEGER NOT
NULL /* Why not a DATETIME column? */ CHECK (accyear BETWEEN 1900 AND
2100), accperiod INTEGER NOT NULL CHECK (accperiod BETWEEN 1 AND 12),
amount NUMERIC(10,2), PRIMARY KEY (acccode,accyear,accperiod)) ;
Try this:
SELECT C.acccode, C.accyear, C.accperiod, C.amount,
SUM(P.amount)-C.amount AS opening_balance,
C.amount,
SUM(P.amount) AS closing_balance
FROM nlmovement AS C, nlmovement AS P
WHERE C.acccode = P.acccode
AND (C.accyear > P.accyear
OR (C.accyear = P.accyear
AND C.accperiod >= P.accperiod))
GROUP BY C.acccode, C.accyear, C.accperiod, C.amount ;
In general it's unwise to store calculations in the database because
any kind of redundancy can lead to inconsistencies and incorrect
results. Don't add the balance columns to a table - just calculate them
in your queries and reports (in fact a reporting tool will probably
calculate the running balance more efficiently than my query will.)
Hope this helps.
David Portas
SQL Server MVP
--sql

need help - SP to move data

Hello
I am trying to create a stored procedure to move a ROW from Table1 to Table
based on a coditon.
Eg. I want to transfer an item with ITEMNO = '001202' FROM currentstock TO
old_stock table.
I want to be able to pass the value for ITEMNO from my code which calls the
SP.
Please help me
TIA
Mackok, I have done myself.
Thanks
"MackS" <mackS@.yahooa.com> wrote in message
news:OWiNi8H$FHA.228@.TK2MSFTNGP12.phx.gbl...
> Hello
> I am trying to create a stored procedure to move a ROW from Table1 to
> Table based on a coditon.
> Eg. I want to transfer an item with ITEMNO = '001202' FROM currentstock TO
> old_stock table.
> I want to be able to pass the value for ITEMNO from my code which calls
> the SP.
> Please help me
> TIA
> Mack
>

Need Experts on Vb.net CLR intergration problem

Hi all I have the following CLR stored procedure :

Partial Public Class StoredProcedures

<Microsoft.SqlServer.Server.SqlProcedure()> _

Public Shared Sub sssGetActiveRepositoryByTitle( _

ByVal title As String)

' Add your code here

Using conn As New SqlConnection("context connection=true")

Dim objCommand As New SqlCommand()

Dim TitleParam As New SqlParameter("@.Title", SqlDbType.VarChar, 100)

TitleParam.Value = title

objCommand.Connection = conn

conn.Open()

'build the delete command

objCommand.CommandText = _

"select * from sstRepository where IsActive = 1 and Title =" & TitleParam.Value.ToString

SqlContext.Pipe.ExecuteAndSend(objCommand)

conn.Close()

End Using

End Sub

Now I have a windows service in my data layer that needs to access this stored procedure and convert it into a dataaset to pass to the client application :

Imports System.Data.SqlClient

Imports NBS.SURVEYSDATABASEservice.DBMS

Public Class clsClient

' it inherits the stored procedures from the DBMS class which is the name

'of the CLR dll

Inherits StoredProcedures

Public Function GetClientByVirtualPath(ByVal pstrVirtualPath As String) As DataSet

Try

Dim i As SqlDataReader

'parameters are stored in an array (zero based) for use in the base class

Dim parmArrSqlParms(0) As SqlClient.SqlParameter

' Dim fff As Int32

i = sdsGetClientByVirtualPath(pstrVirtualPath)

' Return MyBase.RunProcedure("dbo.sdsGetClientByVirtualPath", parmArrSqlParms)

Catch ex As Exception

'log the error

'cLogger.LogMessage("ACME", "SampleApplication", Logger.EntryTypes.RunError, System.Environment.MachineName, "clsDemoClass.SelectAllCompanies", ex.Message)

'raise the error to the caller for handling

Throw ex

End Try

End Function

I've tried a bunch of different things to no avail the error I keep getting trying to access the sqlpipe resulsts is " this expressions does not return any values"

any ideas ? I am basically converting around TSQL 50 stored procs into managed CLR code and the CLR funtions are created but I am really having problems accessing the resuluts on the client end .

Help please !

Before moving 50 TSQL stored proc into managed code, have you considered our TSQL vs CLR guidelines located here?

Need Experts on Vb.net CLR intergration problem

Hi all I have the following CLR stored procedure :

Partial Public Class StoredProcedures

<Microsoft.SqlServer.Server.SqlProcedure()> _

Public Shared Sub sssGetActiveRepositoryByTitle( _

ByVal title As String)

' Add your code here

Using conn As New SqlConnection("context connection=true")

Dim objCommand As New SqlCommand()

Dim TitleParam As New SqlParameter("@.Title", SqlDbType.VarChar, 100)

TitleParam.Value = title

objCommand.Connection = conn

conn.Open()

'build the delete command

objCommand.CommandText = _

"select * from sstRepository where IsActive = 1 and Title =" & TitleParam.Value.ToString

SqlContext.Pipe.ExecuteAndSend(objCommand)

conn.Close()

End Using

End Sub

Now I have a windows service in my data layer that needs to access this stored procedure and convert it into a dataaset to pass to the client application :

Imports System.Data.SqlClient

Imports NBS.SURVEYSDATABASEservice.DBMS

Public Class clsClient

' it inherits the stored procedures from the DBMS class which is the name

'of the CLR dll

Inherits StoredProcedures

Public Function GetClientByVirtualPath(ByVal pstrVirtualPath As String) As DataSet

Try

Dim i As SqlDataReader

'parameters are stored in an array (zero based) for use in the base class

Dim parmArrSqlParms(0) As SqlClient.SqlParameter

' Dim fff As Int32

i = sdsGetClientByVirtualPath(pstrVirtualPath)

' Return MyBase.RunProcedure("dbo.sdsGetClientByVirtualPath", parmArrSqlParms)

Catch ex As Exception

'log the error

'cLogger.LogMessage("ACME", "SampleApplication", Logger.EntryTypes.RunError, System.Environment.MachineName, "clsDemoClass.SelectAllCompanies", ex.Message)

'raise the error to the caller for handling

Throw ex

End Try

End Function

I've tried a bunch of different things to no avail the error I keep getting trying to access the sqlpipe resulsts is " this expressions does not return any values"

any ideas ? I am basically converting around TSQL 50 stored procs into managed CLR code and the CLR funtions are created but I am really having problems accessing the resuluts on the client end .

Help please !

Before moving 50 TSQL stored proc into managed code, have you considered our TSQL vs CLR guidelines located here?

Need Dirxn --> How to schedule recurring stored procedure executio

Hi,
I need to schedule a stored procedure to run on a recurring basis. It would
be acceptable if the procedure ran 1x per minute though it would be better if
I could schedule & re-schedule procedure start time from within a trigger or
other stored procedure. The recurring stored procedure is NOT an
administrative maintenance task. The recurring procedure calls other stored
procedures across servers to gather data for the application.
Question:
1) What's the preferred way to schedule & reschedule recurring execution of
an application's stored procedures? From reading Books Online I gather that
SQL Server Agent and DTS can both schedule tasks? Is one of those two
approaches appropriate for my needs?
--RichardRichard,
You can place your stored proc invocation into a SQLAgent T-SQL job step.
You can invoke a SQLAgent job using sp_start_job. You can schedule this
job by creating a SQLAgent Job Schedule. You can change the Job Schedule
using sp_update_jobschedule.
So, your "other" stored proc can invoke sp_update_jobschedule to change
the frequency that the job executes.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Richard wrote:
> Hi,
> I need to schedule a stored procedure to run on a recurring basis. It would
> be acceptable if the procedure ran 1x per minute though it would be better if
> I could schedule & re-schedule procedure start time from within a trigger or
> other stored procedure. The recurring stored procedure is NOT an
> administrative maintenance task. The recurring procedure calls other stored
> procedures across servers to gather data for the application.
> Question:
> 1) What's the preferred way to schedule & reschedule recurring execution of
> an application's stored procedures? From reading Books Online I gather that
> SQL Server Agent and DTS can both schedule tasks? Is one of those two
> approaches appropriate for my needs?
> --Richard
>|||I agree with Mark, use SQL Agent for this.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:u7AHIUE3EHA.2312@.TK2MSFTNGP15.phx.gbl...
> Richard,
> You can place your stored proc invocation into a SQLAgent T-SQL job step.
> You can invoke a SQLAgent job using sp_start_job. You can schedule this
> job by creating a SQLAgent Job Schedule. You can change the Job Schedule
> using sp_update_jobschedule.
> So, your "other" stored proc can invoke sp_update_jobschedule to change
> the frequency that the job executes.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
> Richard wrote:
> > Hi,
> >
> > I need to schedule a stored procedure to run on a recurring basis. It
would
> > be acceptable if the procedure ran 1x per minute though it would be
better if
> > I could schedule & re-schedule procedure start time from within a
trigger or
> > other stored procedure. The recurring stored procedure is NOT an
> > administrative maintenance task. The recurring procedure calls other
stored
> > procedures across servers to gather data for the application.
> >
> > Question:
> >
> > 1) What's the preferred way to schedule & reschedule recurring execution
of
> > an application's stored procedures? From reading Books Online I gather
that
> > SQL Server Agent and DTS can both schedule tasks? Is one of those two
> > approaches appropriate for my needs?
> >
> > --Richard
> >

Need Dirxn --> How to schedule recurring stored procedure executio

Hi,
I need to schedule a stored procedure to run on a recurring basis. It would
be acceptable if the procedure ran 1x per minute though it would be better if
I could schedule & re-schedule procedure start time from within a trigger or
other stored procedure. The recurring stored procedure is NOT an
administrative maintenance task. The recurring procedure calls other stored
procedures across servers to gather data for the application.
Question:
1) What's the preferred way to schedule & reschedule recurring execution of
an application's stored procedures? From reading Books Online I gather that
SQL Server Agent and DTS can both schedule tasks? Is one of those two
approaches appropriate for my needs?
--Richard
Richard,
You can place your stored proc invocation into a SQLAgent T-SQL job step.
You can invoke a SQLAgent job using sp_start_job. You can schedule this
job by creating a SQLAgent Job Schedule. You can change the Job Schedule
using sp_update_jobschedule.
So, your "other" stored proc can invoke sp_update_jobschedule to change
the frequency that the job executes.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Richard wrote:
> Hi,
> I need to schedule a stored procedure to run on a recurring basis. It would
> be acceptable if the procedure ran 1x per minute though it would be better if
> I could schedule & re-schedule procedure start time from within a trigger or
> other stored procedure. The recurring stored procedure is NOT an
> administrative maintenance task. The recurring procedure calls other stored
> procedures across servers to gather data for the application.
> Question:
> 1) What's the preferred way to schedule & reschedule recurring execution of
> an application's stored procedures? From reading Books Online I gather that
> SQL Server Agent and DTS can both schedule tasks? Is one of those two
> approaches appropriate for my needs?
> --Richard
>
|||I agree with Mark, use SQL Agent for this.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:u7AHIUE3EHA.2312@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> Richard,
> You can place your stored proc invocation into a SQLAgent T-SQL job step.
> You can invoke a SQLAgent job using sp_start_job. You can schedule this
> job by creating a SQLAgent Job Schedule. You can change the Job Schedule
> using sp_update_jobschedule.
> So, your "other" stored proc can invoke sp_update_jobschedule to change
> the frequency that the job executes.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
> Richard wrote:
would[vbcol=seagreen]
better if[vbcol=seagreen]
trigger or[vbcol=seagreen]
stored[vbcol=seagreen]
of[vbcol=seagreen]
that[vbcol=seagreen]

Need debugging help--stored procedure to move database objects

Hi all--I'm adapting a stored procedure to work on transferring SQL Server 2005 tables and other objects owned under the dbo schema on a database to another non-dbo schema. Given that, I'm trying to use an "if...else" to prevent dbo-owned stored procedures and tables like dtproperties from moving with the rest of the objects to the non-dbo schema. Here's the code:

if exists (select * from sys.objects where object_id = object_id(N'[dbo].[chObjOwner]') and OBJECTPROPERTY(object_id, N'IsProcedure') = 1)

drop procedure [dbo].[chObjOwner]

GO

SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON

GO

CREATE proc chObjOwner( @.usrName varchar(20), @.newUsrName varchar(50))

as

-- @.usrName is the current user

-- @.newUsrName is the new user

set nocount on

declare @.uid int -- UID of the user

declare @.objName varchar(50) -- Object name owned by user

declare @.currObjName varchar(50) -- Checks for existing object owned by new user

declare @.outStr varchar(256) -- SQL command with 'alter schema', 'alter authorization'

set @.uid = user_id(@.usrName)

declare chObjOwnerCur cursor static

for

select name from sys.objects where schema_id = @.uid

open chObjOwnerCur

if @.@.cursor_rows = 0

begin

print 'Error: No objects owned by ' + @.usrName

close chObjOwnerCur

deallocate chObjOwnerCur

return 1

end

fetch next from chObjOwnerCur into @.objName

while @.@.fetch_status = 0

begin

set @.currObjName = @.newUsrName + "." + @.objName

if (object_id(@.currObjName) > 0)

print 'WARNING *** ' + @.currObjName + ' already exists ***'

if @.currObjName in ('dt_addtosourcecontrol','dt_addtosourcecontrol_u','dt_adduserobject','dt_adduserobject_vcs','dt_checkinobject','dt_checkinobject_u','dt_checkoutobject','dt_checkoutobject_u','dt_displayoaerror','dt_displayoaerror_u','dt_droppropertiesbyid','dt_dropuserobjectbyid','dt_generateansiname','dt_getobjwithprop','dt_getobjwithprop_u','dt_getpropertiesbyid','dt_getpropertiesbyid_u','dt_getpropertiesbyid_vcs','dt_getpropertiesbyid_vcs_u','dt_isundersourcecontrol','dt_isundersourcecontrol_u','dt_removefromsourcecontrol','dt_setpropertybyid','dt_setpropertybyid_u','dt_validateloginparams','dt_validateloginparams_u','dt_vcsenabled','dt_verstamp006','dt_verstamp007','dt_whocheckedout','dt_whocheckedout_u','dtproperties')

begin

print 'WARNING *** ' + @.currObjName + ' is a system procedure'

end

else

begin

set @.outStr = "alter authorization on " + @.usrName + "." + @.objName + " to " + @.newUsrName

print @.outStr

set @.outStr = "alter schema " + @.newUsrName + " transfer " + @.usrName + "." + @.objName

print @.outStr

print 'go'

fetch next from chObjOwnerCur into @.objName

end

close chObjOwnerCur

deallocate chObjOwnerCur

set nocount off

return 0

GO

SET QUOTED_IDENTIFIER OFF

SET ANSI_NULLS ON

GO

Here's the error I get:

Msg 102, Level 15, State 1, Procedure chObjOwner, Line 51

Incorrect syntax near '0'.

I know that the cursor is having a problem closing and deallocating, but am not sure why the error is happening at this point. Any ideas?

Thanks!

One of your 'begin' s has no 'end'.

Need Assistance Creating a stored procedure

Hi,

I'm trying to work around a bug that our helpdesk software has. When a new
issue is created, it cannot automatically default 2 fields to the value of
No like we need it to.

I have a field called "Audited" and one called "Billed to Client". When a
new issue is openned, it just leaves the value as Null in the database
instead of a value of No.

I would like to create a stored procedure and schedule it to run every 10
minutes to change any value of Null in those columns to No.

Database: bridgetrak
Table: Issues
Column: Audited
Column: Billed

If someone could help me out that would be great! I just don't have very
much experience with SQL statements.

Please email me at shawnf@.sccnet.com

Thanks,
ShawnHi Shawn,

A much better way would be to set up default values of "No" for those
columns and disallow nulls. You can do this through Enterprise manager or
through an Alter Table Query. A stored procedure for this situation is
unnecessary and highly unadvisable.

Regards,

Tyler
"Shawn Fletcher" <shawnf@.sccnet.com> wrote in message
news:40f6a897$0$49110$8f4e7992@.newsreader.goldenga te.net...
> Hi,
> I'm trying to work around a bug that our helpdesk software has. When a
new
> issue is created, it cannot automatically default 2 fields to the value of
> No like we need it to.
> I have a field called "Audited" and one called "Billed to Client". When a
> new issue is openned, it just leaves the value as Null in the database
> instead of a value of No.
> I would like to create a stored procedure and schedule it to run every 10
> minutes to change any value of Null in those columns to No.
> Database: bridgetrak
> Table: Issues
> Column: Audited
> Column: Billed
> If someone could help me out that would be great! I just don't have very
> much experience with SQL statements.
> Please email me at shawnf@.sccnet.com
> Thanks,
> Shawn|||Thank you for your help Tyler,

I tried your suggestion, however after doing that, the helpdesk software
would not save a new issue so I had to change it back to allow nulls and
undo the default value of No. That would have worked great if the program
didn't suck.

Any other suggestions? The only work around I can think of is the stored
procedure. It's much better then currently connecting with Access and doing
a Search and replace.

Thanks,
Shawn

"Tyler Hudson" <TylerH@.Spam.MeNOTallpax.com> wrote in message
news:cd6h0a$7i4$1@.news.datasync.com...
> Hi Shawn,
> A much better way would be to set up default values of "No" for those
> columns and disallow nulls. You can do this through Enterprise manager or
> through an Alter Table Query. A stored procedure for this situation is
> unnecessary and highly unadvisable.
>
> Regards,
> Tyler
> "Shawn Fletcher" <shawnf@.sccnet.com> wrote in message
> news:40f6a897$0$49110$8f4e7992@.newsreader.goldenga te.net...
> > Hi,
> > I'm trying to work around a bug that our helpdesk software has. When a
> new
> > issue is created, it cannot automatically default 2 fields to the value
of
> > No like we need it to.
> > I have a field called "Audited" and one called "Billed to Client". When
a
> > new issue is openned, it just leaves the value as Null in the database
> > instead of a value of No.
> > I would like to create a stored procedure and schedule it to run every
10
> > minutes to change any value of Null in those columns to No.
> > Database: bridgetrak
> > Table: Issues
> > Column: Audited
> > Column: Billed
> > If someone could help me out that would be great! I just don't have
very
> > much experience with SQL statements.
> > Please email me at shawnf@.sccnet.com
> > Thanks,
> > Shawn|||It sounds as though the helpdesk software is specifying insert values of
Null for those columns. If you can edit the helpdesk software, I would go
that route. If not, try using a trigger.

CREATE TRIGGER Issues_INSUPD
ON Issues
FOR INSERT, UPDATE
AS

UPDATE Issues
SET Audited = 'No'
WHERE
Audited IS NULL AND
<keyfieldgoeshere> IN (SELECT <keyfieldgoeshere> FROM INSERTED)

UPDATE Issues
SET Billed = 'No'
WHERE
Billed IS NULL AND
<keyfieldgoeshere> IN (SELECT <keyfieldgoeshere> FROM INSERTED)

"Shawn Fletcher" <shawnf@.sccnet.com> wrote in message
news:40f8031e$0$63722$8f4e7992@.newsreader.goldenga te.net...
> Thank you for your help Tyler,
> I tried your suggestion, however after doing that, the helpdesk software
> would not save a new issue so I had to change it back to allow nulls and
> undo the default value of No. That would have worked great if the program
> didn't suck.
> Any other suggestions? The only work around I can think of is the stored
> procedure. It's much better then currently connecting with Access and
doing
> a Search and replace.
> Thanks,
> Shawn
>
> "Tyler Hudson" <TylerH@.Spam.MeNOTallpax.com> wrote in message
> news:cd6h0a$7i4$1@.news.datasync.com...
> > Hi Shawn,
> > A much better way would be to set up default values of "No" for
those
> > columns and disallow nulls. You can do this through Enterprise manager
or
> > through an Alter Table Query. A stored procedure for this situation is
> > unnecessary and highly unadvisable.
> > Regards,
> > Tyler
> > "Shawn Fletcher" <shawnf@.sccnet.com> wrote in message
> > news:40f6a897$0$49110$8f4e7992@.newsreader.goldenga te.net...
> > > Hi,
> > > > I'm trying to work around a bug that our helpdesk software has. When
a
> > new
> > > issue is created, it cannot automatically default 2 fields to the
value
> of
> > > No like we need it to.
> > > > I have a field called "Audited" and one called "Billed to Client".
When
> a
> > > new issue is openned, it just leaves the value as Null in the database
> > > instead of a value of No.
> > > > I would like to create a stored procedure and schedule it to run every
> 10
> > > minutes to change any value of Null in those columns to No.
> > > > Database: bridgetrak
> > > Table: Issues
> > > Column: Audited
> > > Column: Billed
> > > > If someone could help me out that would be great! I just don't have
> very
> > > much experience with SQL statements.
> > > > Please email me at shawnf@.sccnet.com
> > > > Thanks,
> > > Shawn
> >|||Shawn,

Try this:

create table Issues(Audited char(3), Billed char(3))
go

CREATE TRIGGER TRIGG1 ON Issues
INSTEAD OF INSERT
AS BEGIN
INSERT Issues
SELECT IsNull(Audited, 'No'), IsNull(Billed, 'No')
FROM inserted
END
go

insert Issues values(null, null)
select * from Issues

Shervin

"Shawn Fletcher" <shawnf@.sccnet.com> wrote in message news:<40f8031e$0$63722$8f4e7992@.newsreader.goldengate.ne t>...
> Thank you for your help Tyler,
> I tried your suggestion, however after doing that, the helpdesk software
> would not save a new issue so I had to change it back to allow nulls and
> undo the default value of No. That would have worked great if the program
> didn't suck.
> Any other suggestions? The only work around I can think of is the stored
> procedure. It's much better then currently connecting with Access and doing
> a Search and replace.
> Thanks,
> Shawn
>
> "Tyler Hudson" <TylerH@.Spam.MeNOTallpax.com> wrote in message
> news:cd6h0a$7i4$1@.news.datasync.com...
> > Hi Shawn,
> > A much better way would be to set up default values of "No" for those
> > columns and disallow nulls. You can do this through Enterprise manager or
> > through an Alter Table Query. A stored procedure for this situation is
> > unnecessary and highly unadvisable.
> > Regards,
> > Tyler
> > "Shawn Fletcher" <shawnf@.sccnet.com> wrote in message
> > news:40f6a897$0$49110$8f4e7992@.newsreader.goldenga te.net...
> > > Hi,
> > > > I'm trying to work around a bug that our helpdesk software has. When a
> new
> > > issue is created, it cannot automatically default 2 fields to the value
> of
> > > No like we need it to.
> > > > I have a field called "Audited" and one called "Billed to Client". When
> a
> > > new issue is openned, it just leaves the value as Null in the database
> > > instead of a value of No.
> > > > I would like to create a stored procedure and schedule it to run every
> 10
> > > minutes to change any value of Null in those columns to No.
> > > > Database: bridgetrak
> > > Table: Issues
> > > Column: Audited
> > > Column: Billed
> > > > If someone could help me out that would be great! I just don't have
> very
> > > much experience with SQL statements.
> > > > Please email me at shawnf@.sccnet.com
> > > > Thanks,
> > > Shawn
> >

Need an example of failed CREATE PROC...

This is an example of a working CREATE PROCEDURE.
USE Northwind
GO
CREATE PROC dbo.OverdueOrders
AS
SELECT *
FROM dbo.Orders
WHERE RequiredDate < GETDATE() AND ShippedDate IS Null
What does it mean that "a CREATE PROCEDURE statement cannot be combined with
other TSQL statements in a single batch?" I see a TSQL stmt in this working
one.
Would someone provide me an example, please?
Thanks.
USE Northwind
GO
SELECT *
FROM dbo.Orders
CREATE PROC dbo.OverdueOrders
AS
SELECT *
FROM dbo.Orders
WHERE RequiredDate < GETDATE() AND ShippedDate IS Null
GO
"light_wt" <lightwt@.discussions.microsoft.com> wrote in message
news:8E891A71-BE7D-495A-B134-CC3C1D1E2367@.microsoft.com...
> This is an example of a working CREATE PROCEDURE.
> USE Northwind
> GO
> CREATE PROC dbo.OverdueOrders
> AS
> SELECT *
> FROM dbo.Orders
> WHERE RequiredDate < GETDATE() AND ShippedDate IS Null
> What does it mean that "a CREATE PROCEDURE statement cannot be combined
with
> other TSQL statements in a single batch?" I see a TSQL stmt in this
working
> one.
> Would someone provide me an example, please?
> Thanks.
|||Here you go:
USE Northwind
GO
SELECT * FROM Orders
CREATE PROC dbo.OverdueOrders
AS
SELECT *
FROM dbo.Orders
WHERE RequiredDate < GETDATE()
AND ShippedDate IS NULL
GO
Anith
|||A batch is a set of TSQL statements that are submitted together for
processing. You designate the end of a batch of commands in SQL Server
using the GO statement.
There are only two CREATE statements that can be combined in a single batch.
CREATE DATBASE and CREATE TABLE.
All of the other CREATE statements must be submitted as separate batches.
So:
CREATE DATABASE Frog (....)
CREATE TABLE LilyPad (...)
CREATE TABLE Swim(...)
GO
Would work, but
CREATE TABLE LilyPad(...)
CREATE PROC Croak ...
Would not.
You would have to submit them as:
CREATE TABLE LilyPad...
GO
CREATE PROC Croak...
GO
HTH
Rick Sawtell
MCT, MCSD, MCDBA
"light_wt" <lightwt@.discussions.microsoft.com> wrote in message
news:8E891A71-BE7D-495A-B134-CC3C1D1E2367@.microsoft.com...
> This is an example of a working CREATE PROCEDURE.
> USE Northwind
> GO
> CREATE PROC dbo.OverdueOrders
> AS
> SELECT *
> FROM dbo.Orders
> WHERE RequiredDate < GETDATE() AND ShippedDate IS Null
> What does it mean that "a CREATE PROCEDURE statement cannot be combined
with
> other TSQL statements in a single batch?" I see a TSQL stmt in this
working
> one.
> Would someone provide me an example, please?
> Thanks.
|||Thank you, all.
I appreciate your detailed and good explaination, Rick.
"Rick Sawtell" wrote:
<snip>

Need alittle help with sql statemtn stored procedure

Here is what I am trying to do, If i type to query the stored procedure with two parameters and the two parameters are null, return all rows, else do the query based on the two parameters. Thanks in advance.

Create PROCEDURE [dbo].[Admin_RetrieveCustomerOrders] @.Yearnumint,@.monthnumintASBEGINSET NOCOUNT ON;SELECT o.intOrderId, o.vcCustomerID, o.decTotal, o.dtOrdered,u.vcFirstName, u.vcLastName,(SELECTCOUNT(d.intOrderID)FROM tblOrderDetails dWHERE d.intOrderID = o.intOrderID)AS TotalProductsFROM tblOrders oINNERJOIN tblUserInformation uON o.vcCustomerID = u.vcCustomerIDWHERE YEAR(o.dtOrdered)=@.yearnumANDMONTH(o.dtOrdered)=@.monthnumORDER BY o.dtOrderedDESC END

Thanks Again

Joshua

I think this may work

Essentially you need to modify you WHERE clause to also allow null for the parameters

WHERE (YEAR(o.dtOrdered)=@.yearnumOR @.yearnumisNULL)AND (MONTH(o.dtOrdered)=@.monthnumOR @.monthnumisNULL)

Give the above ago and let us know if it works. 

Need Advise on Stored Procedure

Little rusty on my SQL but here is what I need. Currently have a stored procedure that returns numerical data for 8 different element readings. ( Carbon, Silicon, Sulfur, etc ). This data is then put into a datagrid on an asp.net page and shows the readings for each test.
On my asp .net page... they only want data that is out of the specified ranges to be shown ( EX: > 1.6 AND < 2.0 ). Currently it is setup on the page to change the font color to red of data of its range but now they don't want data to be shown at all if for that one test everything is in its range.
So how could I write my Where clause to check the 8 different element ranges and only need one of them to be true in order to show.
Could I use an If / Then statement?
Any help is appreciated... Sorry this is quickly done but got to have it done soon.

Show your table layout. Also, are the ranges for all elements the same, or element specific?

need a subsetsorted by languageid can somebody help with stored proc..............

Here is part of my stored procedure. I would like to get a subsetsorted back from my query where languageid = @.languageid but I am lost. Can somebody help me out, thanks Newbie

-- Issue query

DECLARE @.sqlnvarchar(4000)

SET @.sql='SELECT [KeywordID], [Keyword], [LanguageID]

FROM

(SELECT [KeywordID], [Keyword], [LanguageID], ROW_NUMBER() OVER(ORDER BY '+ @.sortExpression+') AS RowNum

FROM syl_Keywords) AS KeywordInfo

WHERE RowNum BETWEEN '+CONVERT(nvarchar(10), @.startRowIndex)+

' AND ('+CONVERT(nvarchar(10), @.startRowIndex)+' + '

+CONVERT(nvarchar(10), @.maximumRows)+') - 1 AND [LanguageID] = @.LanguageID'

-- Execute the SQL query

EXECsp_executesql @.sql

If this answers your question... please mark this as the "answer" :)

DECLARE

@.SQLVARCHAR(8000)

SELECT @.SQL='
WITH _temp AS
(SELECT
KeywordID,
Keyword,
LanguageID,
ROW_NUMBER() OVER(ORDER BY '+ @.sortExpression+' ) AS RowNumber
FROM
syl_Keywords
WHERE
LanguageID = '''+ @.LanguageID+''')

SELECT TOP '+CONVERT(VARCHAR, @.maximumRows)+' *
FROM _temp WHERE RowNumber > '+CONVERT(VARCHAR, @.startRowIndex)

EXECsp_executesql @.SQL

Peace,

|||

Hi Nullable, thank you for your reply. I can't get it to work with your example. Here is my original stored procedure that returns a subset sorted by sortexpression which works. What I would like to accomplish is to sort it by LanguageID as well.

setANSI_NULLSON

setQUOTED_IDENTIFIERON

GO

ALTER

PROCEDURE [dbo].[syl_KeywordGetSubsetSorted]

@.sortExpression

nvarchar(50),

@.startRowIndex

int,

@.maximumRows

int

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from-- interfering with SELECT statements.SETNOCOUNTONBEGINTRYIFLEN(@.sortExpression)= 0SET @.sortExpression='KeywordID'-- Since @.startRowIndex is zero-based in the data Web control, but one-based w/ROW_NUMBER(), incrementSET @.startRowIndex= @.startRowIndex+ 1-- Issue queryDECLARE @.sqlnvarchar(4000)SET @.sql='SELECT [KeywordID], [Keyword], [LanguageID]

FROM

(SELECT [KeywordID], [Keyword], [LanguageID], ROW_NUMBER() OVER(ORDER BY '

+ @.sortExpression+') AS RowNum

FROM syl_Keywords) AS KeywordInfo

WHERE RowNum BETWEEN '

+CONVERT(nvarchar(10), @.startRowIndex)+' AND ('+CONVERT(nvarchar(10), @.startRowIndex)+' + '+CONVERT(nvarchar(10), @.maximumRows)+') - 1'

-- Execute the SQL queryEXECsp_executesql @.sqlRETURNENDTRY

BEGIN

CATCH--Execute LogError SPEXECUTE [dbo].[syl_LogError];--Being in a Catch Block indicates failure.--Force RETURN to -1 for consistency (other return values are generated, such as -6).RETURN-1ENDCATCH

END

|||

newbie06:

(SELECT [KeywordID], [Keyword], [LanguageID], ROW_NUMBER() OVER(ORDER BY '+ @.sortExpression+') AS RowNum

FROM syl_Keywords) AS KeywordInfo

Change that to:

(SELECT [KeywordID], [Keyword], [LanguageID], ROW_NUMBER() OVER(ORDER BY '+ @.sortExpression+', LanguageID) AS RowNum

FROM syl_Keywords) AS KeywordInfo

|||

Here is the working portion of the Stored Proc:

-- Issue query

DECLARE @.sqlnvarchar(4000)SET @.sql='SELECT [KeywordID], [Keyword], [LanguageID]

FROM

(SELECT [KeywordID], [Keyword], [LanguageID], ROW_NUMBER() OVER(ORDER BY '

+ @.sortExpression+') AS RowNum

FROM syl_Keywords

WHERE LanguageID = '

+CONVERT(nvarchar(10), @.LanguageID)+' )

AS KeywordInfo

WHERE RowNum BETWEEN '

+CONVERT(nvarchar(10), @.startRowIndex)+' AND ('+CONVERT(nvarchar(10), @.startRowIndex)+' + '+CONVERT(nvarchar(10), @.maximumRows)+') - 1'

Need a little help with Join (Beginner)

Im writing this stored procedure and just need to add one more column to the mix. But i cant recall exactly what to do with joins. I have examples, but they all look alittle different. Anyways, i realized that the column i want to add (its highlighted) Needs to have a join. Here is my code. Can anyone explain to me, how i join this with the rest. IT is located in a table called Budget_Reporting_Group and the column is called Budget_Reporting_Group_Description. As you can see, I have another Table called Salesperson_Purchaser, Im pretty sure these two tables are relational by a Salesperson_Purchaser_ID. How would i do the join? If anything, can you display what it would look like (IE yourDatabase.dbo.TableName as Table_Name ON etc etc) I completely forgot the format.

USE [RC_STAT]

GO

/****** Object: StoredProcedure [dbo].[PROC_RPT_EXPENSE_DETAIL_T&E_MKT_Copy] Script Date: 06/28/2007 09:49:08 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTERPROCEDURE [dbo].[PROC_RPT_EXPENSE_DETAIL_T&E_MKT_COPY]

(@.Region int=Null)

AS

BEGIN

SELECT

[Item_Ledger_Posting_Datetime] Post_Date

,'SMP' Budget_Type

,'Invoice'as Document_Type

,[Item_Ledger_Document_No] Document_Number

,[Item_Description]+' '+'('+[Item_No]+')' Entry_Description

,ISNULL(RC_STAT.dbo.udf_Correct_Price(Item_No, Item_Ledger_Posting_Datetime,'SALESAMP')*-1*Item_Ledger_Invoiced_Qty,Item_Ledger_Cost_Posted_GL *-1 ) Amount

,-1*[Item_Ledger_Invoiced_Qty] Quantity

,Customer_Name

,'' External_Doc_no

,[Item_Ledger_Sales_Responsible] SR_Code

,[Item_Ledger_Mars_Period_Code] ThePeriod

,[Item_Ledger_Mars_Year] TheYear

,Territory.Name AS Territory_Name

,Region.Region AS Region_Name

,Budget_Reporting_Group.Budget_Reporting_Group_Description

FROM [NavisionReplication].[dbo].[Qry_Item_Ledger_Detail]

INNERJOIN NavisionReplication.dbo.Tbl_Salesperson_Purchaser AS Salesperson_Purchaser

ON [Item_Ledger_Sales_Responsible] = Salesperson_Purchaser.SalesPerson_Purchaser_Code

INNERJOIN RC_DWDB_INSTANCE_1.dbo.Territory AS Territory

ON Territory.Code = Salesperson_Purchaser.Territory_Code

INNERJOIN NavisionReplication.dbo.Tbl_Budget_Reporting as Budget_Reporting

ON Salesperson_Purchaser.Salesperson_Purchaser_Code=Budget_Reporting.Salesperson_Purchaser_Code

LEFTOUTERJOIN RC_DWDB_INSTANCE_1.dbo.Region AS Region

ON Territory.Region_Key = Region.Region_Key

WHERE Region.Region_Key = @.Region

AND [Item_Ledger_Mars_Year] = 2007

UNIONALL

SELECT

[GL_Entry_Posting_DateTime]

,[Budget_Type_Code]

,[Document_Type]

,[GL_Entry_Document_No]

,[GL_Entry_Description]

,[GL_Entry_Amount]

,0 Quantity

,[User_ID]

,[GL_Entry_External_Document_No]

,[Sales_Responsible]

,[Mars_Period_Code]

,[Mars_Year]

,Territory.Name AS Territory_Name

,Region.Region AS Region_Name

,Budget_Reporting_Group.Budget_Reporting_Group_Description

FROM [NavisionReplication].[dbo].[Tbl_GL_entry] GL_entry

INNERJOIN [RC_DWDB_INSTANCE_1].[dbo].[Tbl_Budget_Accounts] Budget_Accounts

ON Budget_Accounts.[GL_Account_No] = GL_entry.[GL_Account]

INNERJOIN NavisionReplication.dbo.Tbl_Salesperson_Purchaser AS Salesperson_Purchaser

ON GL_entry.[Sales_Responsible] = Salesperson_Purchaser.SalesPerson_Purchaser_Code

INNERJOIN RC_DWDB_INSTANCE_1.dbo.Territory AS Territory

ON Territory.Code = Salesperson_Purchaser.Territory_Code

LEFTOUTERJOIN RC_DWDB_INSTANCE_1.dbo.Region AS Region

ON Territory.Region_Key = Region.Region_Key

INNERJOIN NavisionReplication.dbo.Tbl_Budget_Reporting as Budget_Reporting

ON Salesperson_Purchaser.Salesperson_Purchaser_Code=Budget_Reporting.Salesperson_Purchaser_Code

WHERE GL_entry.[Mars_Year] = 2007

END

Code Snippet

INNER JOIN Budget_Reporting_Group

ON BUDGET_REPORTING_GROUP.Salesperson_Purchaser_ID = SALESPERSON_PURCHASER.Salesperson_Purchaser_ID

|||

I still keep getting this error:

sg 208, Level 16, State 1, Procedure PROC_RPT_EXPENSE_DETAIL_T&E_MKT_COPY, Line 7

Invalid object name 'Tbl_Budget_Reporting_Group'.

Msg 208, Level 16, State 1, Procedure PROC_RPT_EXPENSE_DETAIL_T&E_MKT_COPY, Line 7

Invalid object name 'Tbl_Budget_Reporting_Group'.

Need %complete stat from stored procedure to vb

Is there a way to call a SQLServer stored procedure from within VB6
and have the % completion stat be returned to the calling program as
it is updated? Our clients hate seeing static screens and while this
procedure is processing I want to be able to display it's progress.
Thanks!Not from a single statement, there's not any way to get back the completion
status from a query.
Some operations, which are predictable, like BACKUP, has an option to get ST
ATS back, but not for
general DML statements. If your procedure is executing *several* statements,
you can use RAISERROR
using the NO_WAIT option to make SQL server flush the output buffer for each
RAISERROR statement.
For a single query, fake it... :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mark V" <mgvincent@.excite.com> wrote in message
news:96887154.0411170859.77244be0@.posting.google.com...
> Is there a way to call a SQLServer stored procedure from within VB6
> and have the % completion stat be returned to the calling program as
> it is updated? Our clients hate seeing static screens and while this
> procedure is processing I want to be able to display it's progress.
> Thanks!

Need %complete stat from stored procedure to vb

Is there a way to call a SQLServer stored procedure from within VB6
and have the % completion stat be returned to the calling program as
it is updated? Our clients hate seeing static screens and while this
procedure is processing I want to be able to display it's progress.
Thanks!Not from a single statement, there's not any way to get back the completion status from a query.
Some operations, which are predictable, like BACKUP, has an option to get STATS back, but not for
general DML statements. If your procedure is executing *several* statements, you can use RAISERROR
using the NO_WAIT option to make SQL server flush the output buffer for each RAISERROR statement.
For a single query, fake it... :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mark V" <mgvincent@.excite.com> wrote in message
news:96887154.0411170859.77244be0@.posting.google.com...
> Is there a way to call a SQLServer stored procedure from within VB6
> and have the % completion stat be returned to the calling program as
> it is updated? Our clients hate seeing static screens and while this
> procedure is processing I want to be able to display it's progress.
> Thanks!

Need %complete stat from stored procedure to vb

Is there a way to call a SQLServer stored procedure from within VB6
and have the % completion stat be returned to the calling program as
it is updated? Our clients hate seeing static screens and while this
procedure is processing I want to be able to display it's progress.
Thanks!
Not from a single statement, there's not any way to get back the completion status from a query.
Some operations, which are predictable, like BACKUP, has an option to get STATS back, but not for
general DML statements. If your procedure is executing *several* statements, you can use RAISERROR
using the NO_WAIT option to make SQL server flush the output buffer for each RAISERROR statement.
For a single query, fake it... :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mark V" <mgvincent@.excite.com> wrote in message
news:96887154.0411170859.77244be0@.posting.google.c om...
> Is there a way to call a SQLServer stored procedure from within VB6
> and have the % completion stat be returned to the calling program as
> it is updated? Our clients hate seeing static screens and while this
> procedure is processing I want to be able to display it's progress.
> Thanks!