How To Script

I need to update a value in IMA_GLInvAcctNbr from 11283 to 11500. the table
name is dbo.item. What would my script look like to do this in SQL server
2005? The column has a silver key by it too in SQL Server Management
Studio.

ThanksBrian (b.houghtby@.eaglecrusher.com) writes:

Quote:

Originally Posted by

I need to update a value in IMA_GLInvAcctNbr from 11283 to 11500. the
table name is dbo.item. What would my script look like to do this in
SQL server 2005? The column has a silver key by it too in SQL Server
Management Studio.

UPDATE dbo.item
SET IMA_GLInvAcctNbr = 11500
WHERE IMA_GLInvAcctNbr = 11283

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||What does the silver key mean?

"Erland Sommarskog" <esquel@.sommarskog.sewrote in message
news:Xns9947EFB6764B9Yazorman@.127.0.0.1...

Quote:

Originally Posted by

Brian (b.houghtby@.eaglecrusher.com) writes:

Quote:

Originally Posted by

>I need to update a value in IMA_GLInvAcctNbr from 11283 to 11500. the
>table name is dbo.item. What would my script look like to do this in
>SQL server 2005? The column has a silver key by it too in SQL Server
>Management Studio.

>
UPDATE dbo.item
SET IMA_GLInvAcctNbr = 11500
WHERE IMA_GLInvAcctNbr = 11283
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

|||What does the silver key mean?

It means that it is in a foreign key relationship to another table.

On Jun 7, 9:51 am, "Brian" <b.hough...@.eaglecrusher.comwrote:

Quote:

Originally Posted by

>
"Erland Sommarskog" <esq...@.sommarskog.sewrote in message
>
news:Xns9947EFB6764B9Yazorman@.127.0.0.1...
>
>
>

Quote:

Originally Posted by

Brian (b.hough...@.eaglecrusher.com) writes:

Quote:

Originally Posted by

I need to update a value in IMA_GLInvAcctNbr from 11283 to 11500. the
table name is dbo.item. What would my script look like to do this in
SQL server 2005? The column has a silver key by it too in SQL Server
Management Studio.

>

Quote:

Originally Posted by

UPDATE dbo.item
SET IMA_GLInvAcctNbr = 11500
WHERE IMA_GLInvAcctNbr = 11283

>

Quote:

Originally Posted by

--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se

>

Quote:

Originally Posted by

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...downloads/books...
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ons/books.mspx- Hide quoted text -

>
- Show quoted text -

How to save Hash Value on the SQL Server?

How to save Hash Value on the SQL Server?

What SQL Server data type to use?

Hash Value looks like: {19,55,198,12,420,13,36,107,109,25,82,23,510,552,931,996,478,442,130,200}

Shomer @. http://www.xtremejewelery.com

http://www.xtremejewelery.com/watches/specials/

--

From: Shomer

Hi
The best way to store the hash value is to use varbinary data type. If you have a binary representation, you can probably use that to store it directly. If all you have is a representation similar to that above, you can probably use two bytes to represent each of the integers in the sequence.

- Christian

How to return value from Stored Procedure(SQL Server) into c

Hi,
Yes. Exactly we are using the ADODB parameters. Here with i have
attached the coded snippet.
pCommand->Parameters->Append(
pCommand->CreateParameter(_bstr_t("strSalesOrderNo"), adVarChar,
adParamInput, 64, vstrSalesOrderNo));
pCommand->Parameters->Append(
pCommand->CreateParameter(_bstr_t("strToolName"), adVarChar,
adParamInput,
200, vstrToolName));
pCommand->Parameters->Append(
pCommand->CreateParameter(_bstr_t("strToolAddress"), adVarChar,
adParamInput, 64, vstrToolAddress));
pCommand->Parameters->Append(
pCommand->CreateParameter(_bstr_t("Output"), adInteger, adParamOutput, 4
,
&viResult));
pRs = pCommand->Execute( &vNull, &vNull, adCmdStoredProc);
pRs->Close();
_variant_t v;
v.vt = VT_BSTR;
v.bstrVal =_bstr_t("Output");
_variant_t vResult;
vResult = pCommand->Parameters->GetItem(v)->Value;
Here when we debug vResult, it shows VT_EMPTY.
Does this code has issues with this ?
Thanks in Advance,
Ravindran.Hi Ravi
i think u can try vResult = pCommand(0);
i am not sure if it works in VC, but this is the was i do in VB
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"ravip" wrote:

> Hi,
> Yes. Exactly we are using the ADODB parameters. Here with i have
> attached the coded snippet.
> pCommand->Parameters->Append(
> pCommand->CreateParameter(_bstr_t("strSalesOrderNo"), adVarChar,
> adParamInput, 64, vstrSalesOrderNo));
> pCommand->Parameters->Append(
> pCommand->CreateParameter(_bstr_t("strToolName"), adVarChar,
> adParamInput,
> 200, vstrToolName));
> pCommand->Parameters->Append(
> pCommand->CreateParameter(_bstr_t("strToolAddress"), adVarChar,
> adParamInput, 64, vstrToolAddress));
> pCommand->Parameters->Append(
> pCommand->CreateParameter(_bstr_t("Output"), adInteger, adParamOutput, 4
> ,
> &viResult));
>
> pRs = pCommand->Execute( &vNull, &vNull, adCmdStoredProc);
> pRs->Close();
> _variant_t v;
> v.vt = VT_BSTR;
> v.bstrVal =_bstr_t("Output");
> _variant_t vResult;
> vResult = pCommand->Parameters->GetItem(v)->Value;
> Here when we debug vResult, it shows VT_EMPTY.
> Does this code has issues with this ?
> Thanks in Advance,
> Ravindran.
>
>

How to return value from dynamic query or set the value

Hii I am Varun i have a problem with the dynamic stored procedure

This is my stored procedure

ALTER PROCEDUREdbo.sp_TimeTableAdjustment1

(

@.TeacherID_OnLeavenumeric(9),

@.DateFromdatetime,

@.DateTodatetime, @.UserIDnumeric(9)

)

AS

declare@.flagas numeric(9)

declare@.yearas varchar(4)

set@.year=(selectbatchfrombatchmasterwhereiscurrent=1andisdeleted=0)

if( @.year=null or len(@.year)=0)

set@.year =year(getdate())

exec('if not exists(select * from timetableadjustments_'+@.year+' where datefrom='''+@.datefrom+''' and dateto='''+@.dateto+''' and teacherid_onleave='+@.teacherid_onleave+')

begin

insert into TimeTableAdjustments_'+@.year+' (TeacherID_OnLeave,DateFrom,DateTo,UserID) values ('+@.TeacherID_OnLeave+','''+@.DateFrom+''','''+@.DateTo+''','+@.UserID+');

end')

else

set@.flag=(selecttimetableadjustmentidfromtimetableadjustments_2007wheredatefrom=@.datefromanddateto=@.datetoandteacherid_onleave=@.teacherid_onleave)

return@.flag

--exec('select timetableadjustmentid from timetableadjustments_'+@.year+' where datefrom='''+@.DateFrom+''' and dateto='''+@.DateTo+''' and teacherid_onleave='+@.TeacherID_OnLeave+'

--')

--return @.flag

--exec('@.flag=select timetableadjustmentid from timetableadjustments_'+@.year+' where datefrom='''+@.DateFrom+''' and dateto='''+@.DateTo+''' and teacherid_onleave='+@.TeacherID_OnLeave+'')

And the problem?...

How to return text data type from stored procedure.

Hi all,
I am having one stored procedure which is returing parameter having
text data type.
This paramter has to take value from table which have column with
datatype as text.
How will i set value to parameter having text datatype to value
present in table?
Any help will be truely appreciated.> I am having one stored procedure which is returing parameter having
> text data type.
> This paramter has to take value from table which have column with
> datatype as text.
> How will i set value to parameter having text datatype to value
> present in table?
You would just SELECT it, not set it to a variable. You can't have a
variable of type TEXT. And you can't RETURN anything other than an INT, so
I assume you meant OUTPUT, not RETURN.
In SQL Server 2005 you can use VARCHAR(MAX) which is a first-class
citizen -- meaning you can DECLARE @.foo VARCHAR(MAX) and proceed with
storing 2GB of text in there if you want.
A|||> I am having one stored procedure which is returing parameter having
> text data type.
You can't return anything but an INT, and it is *NOT* meant to return
*DATA* -- return values are meant to return error/status. Single data
elements that are not part of a resultset should be "returned" via an OUTPUT
parameter.
And you can't store TEXT as a local variable... maybe you could use
VARCHAR(MAX) in SQL Server 2005. Otherwise all you can do is SELECT
TextColumn FROM table and have the application consume the result that way.
--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006

How to return text data type from stored procedure.

Hi all,
I am having one stored procedure which is returing parameter having
text data type.
This paramter has to take value from table which have column with
datatype as text.
How will i set value to parameter having text datatype to value
present in table?
Any help will be truely appreciated.> I am having one stored procedure which is returing parameter having
> text data type.
You can't return anything but an INT, and it is *NOT* meant to return
*DATA* -- return values are meant to return error/status. Single data
elements that are not part of a resultset should be "returned" via an OUTPUT
parameter.
And you can't store TEXT as a local variable... maybe you could use
VARCHAR(MAX) in SQL Server 2005. Otherwise all you can do is SELECT
TextColumn FROM table and have the application consume the result that way.
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006

How to Return SqlDataReader and return value (page count) from SPROC

This is my function, it returns SQLDataReader to DATALIST control. Howto return page number with the SQLDataReader set ? sql server 2005,asp.net 2.0

Function get_all_events() As SqlDataReader
Dim myConnection As NewSqlConnection(ConfigurationManager.AppSettings("........."))
Dim myCommand As New SqlCommand("EVENTS_LIST_BY_REGION_ALL", myConnection)
myCommand.CommandType = CommandType.StoredProcedure

Dim parameterState As New SqlParameter("@.State", SqlDbType.VarChar, 2)
parameterState.Value = Request.Params("State")
myCommand.Parameters.Add(parameterState)

Dim parameterPagesize As New SqlParameter("@.pagesize", SqlDbType.Int, 4)
parameterPagesize.Value = 20
myCommand.Parameters.Add(parameterPagesize)

Dim parameterPagenum As New SqlParameter("@.pageNum", SqlDbType.Int, 4)
parameterPagenum.Value = pn1.SelectedPage
myCommand.Parameters.Add(parameterPagenum)

Dim parameterPageCount As New SqlParameter("@.pagecount", SqlDbType.Int, 4)
parameterPageCount.Direction = ParameterDirection.ReturnValue
myCommand.Parameters.Add(parameterPageCount)

myConnection.Open()
'myCommand.ExecuteReader(CommandBehavior.CloseConnection)
'pages = CType(myCommand.Parameters("@.pagecount").Value, Integer)
Return myCommand.ExecuteReader(CommandBehavior.CloseConnection)
End Function

Variable Pages is global integer.

This is what i am calling
DataList1.DataSource = get_all_events()
DataList1.DataBind()

How to return records and also the return value of pagecount ? i tried many options, nothing work. Please help !!. I am struck

please any help ? finally following code works without error, but still not returning any return value (pagecount) along with the list of records.

Dim MyReader As SqlDataReader
Dim myConnection As New SqlConnection(ConfigurationManager.AppSettings("Dx918Aveb8ax81"))
Dim myCommand As New SqlCommand("EVENTS_LIST_BY_REGION_ALL", myConnection)
myCommand.CommandType = CommandType.StoredProcedure

Dim parameterregion As New SqlParameter("@.Region", SqlDbType.VarChar, 5)
parameterregion.Value = Request.Params("reg")
myCommand.Parameters.Add(parameterregion)

Dim parameterPagesize As New SqlParameter("@.pagesize", SqlDbType.Int, 4)
parameterPagesize.Value = 10
myCommand.Parameters.Add(parameterPagesize)

Dim parameterPagenum As New SqlParameter("@.pageNum", SqlDbType.Int, 4)
parameterPagenum.Value = pn1.SelectedPage
myCommand.Parameters.Add(parameterPagenum)

Dim parameterPageCount As New SqlParameter("@.pagecount", SqlDbType.Int, 4)
parameterPageCount.Direction = ParameterDirection.ReturnValue
myCommand.Parameters.Add(parameterPageCount)

myConnection.Open()
MyReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection)
pages = myCommand.Parameters("@.pagecount").Value
DataList1.DataSource = MyReader
DataList1.DataBind()
myCommand.Dispose()
myConnection.Dispose()
Response.Write(pages)

|||Output parameters and return values are not available until after aDataReader is closed (which you should add after your DataBind()).|||thank you. I will try that today!!

How to return multiple columns from FLWOR

I have very simple xml shredding requirement. What I would like to do is to get value of SourcesID and SourcesType returned as columns from the following. Currently I could get both values concatenated but requirement dictate having both values returned as separate columns. Please notice that given statement is a simplified version and I understand using Value function would be an easier solution but I have set of conditions within for that needs to be evaluated before I select SourcesID node. So, I would appreciate if the solution proposed is along the line of given code.

DECLARE @.xml XML

SET @.xml =

'<State>

<SourceState>

<SourcesID>57341</SourcesID>

<SourcesType>50</SourcesType>

</SourceState>

</State>'

SELECT

Element.Val.query(

'for $s in self::node()

return $s//SourcesID/text(),

for $s in self::node()

return $s//SourcesType/text()

') AS SourcesID

FROM @.xml.nodes('/State') AS Element(Val)

Asaf:

I think that I still don't understand what you are looking for in terms of output. Here is my first pass at trying to understand your needs. Please comment so that I might be able to properly target this.

Code Snippet

DECLARE @.xml XML
SET @.xml =
'<State>
<SourceState>
<SourcesID>57341</SourcesID>
<SourcesType>50</SourcesType>
</SourceState>
</State>'
select parsename(cast(SourcesID as varchar), 2) as SourcesID,
substring(parseName(cast(SourcesID as varchar), 1), 2, 99)
as SourcesType
from ( SELECT
Element.Val.query(
'for $s in self::node()/SourceState/SourcesID/text()
return concat(string($s),"."),
for $s in self::node()/SourceState/SourcesType/text()
return string($s)
') AS SourcesID
FROM @.xml.nodes('/State') AS Element(Val)
) x

/*
SourcesID SourcesType
-- -
57341 50
*/

|||

Hi there,

I am sorry for having a bit confusing question but the proposed solution from you was what I needed. What I was hoping, though, was to basically get two columns returned from FLWOR without any concatenation and parsing of returned resultset later on.

Thanks very much.

|||

It is not clear what kind of condition you have but note that you can also apply conditions in path expressions in square brackets so perhaps your problem can be solved by simply applying the condition already when you use the nodes function e.g.

FROM @.xml.nodes('/State[SourceState/SourcesType > 40]') AS Element(Val)

then you can apply the value method to get two columns in the result.

|||

Code Snippet

DECLARE @.xml XML
SET @.xml =
'<State>
<SourceState>
<SourcesID>57341</SourcesID>
<SourcesType>50</SourcesType>
</SourceState>
</State>'
SELECT
Element.Val.query(
'for $s in self::node()
return $s//SourcesID/text()') as a,
Element.val.query(
'for $s in self::node()
return $s//SourcesType/text()') as b
FROM @.xml.nodes('/State') AS Element(Val)

/*
a b
-
57341 50
*/

How to return multiple columns from FLWOR

I have very simple xml shredding requirement. What I would like to do is to get value of SourcesID and SourcesType returned as columns from the following. Currently I could get both values concatenated but requirement dictate having both values returned as separate columns. Please notice that given statement is a simplified version and I understand using Value function would be an easier solution but I have set of conditions within for that needs to be evaluated before I select SourcesID node. So, I would appreciate if the solution proposed is along the line of given code.

DECLARE @.xml XML

SET @.xml =

'<State>

<SourceState>

<SourcesID>57341</SourcesID>

<SourcesType>50</SourcesType>

</SourceState>

</State>'

SELECT

Element.Val.query(

'for $s in self::node()

return $s//SourcesID/text(),

for $s in self::node()

return $s//SourcesType/text()

') AS SourcesID

FROM @.xml.nodes('/State') AS Element(Val)

Asaf:

I think that I still don't understand what you are looking for in terms of output. Here is my first pass at trying to understand your needs. Please comment so that I might be able to properly target this.

Code Snippet

DECLARE @.xml XML
SET @.xml =
'<State>
<SourceState>
<SourcesID>57341</SourcesID>
<SourcesType>50</SourcesType>
</SourceState>
</State>'
select parsename(cast(SourcesID as varchar), 2) as SourcesID,
substring(parseName(cast(SourcesID as varchar), 1), 2, 99)
as SourcesType
from ( SELECT
Element.Val.query(
'for $s in self::node()/SourceState/SourcesID/text()
return concat(string($s),"."),
for $s in self::node()/SourceState/SourcesType/text()
return string($s)
') AS SourcesID
FROM @.xml.nodes('/State') AS Element(Val)
) x

/*
SourcesID SourcesType
-- -
57341 50
*/

|||

Hi there,

I am sorry for having a bit confusing question but the proposed solution from you was what I needed. What I was hoping, though, was to basically get two columns returned from FLWOR without any concatenation and parsing of returned resultset later on.

Thanks very much.

|||

It is not clear what kind of condition you have but note that you can also apply conditions in path expressions in square brackets so perhaps your problem can be solved by simply applying the condition already when you use the nodes function e.g.

FROM @.xml.nodes('/State[SourceState/SourcesType > 40]') AS Element(Val)

then you can apply the value method to get two columns in the result.

|||

Code Snippet

DECLARE @.xml XML
SET @.xml =
'<State>
<SourceState>
<SourcesID>57341</SourcesID>
<SourcesType>50</SourcesType>
</SourceState>
</State>'
SELECT
Element.Val.query(
'for $s in self::node()
return $s//SourcesID/text()') as a,
Element.val.query(
'for $s in self::node()
return $s//SourcesType/text()') as b
FROM @.xml.nodes('/State') AS Element(Val)

/*
a b
-
57341 50
*/

How to return current hour of week?

One week have 168 hours.
How do you create SQl statement that return hour value for specific timestamp??
eg.
If week starts on system from Monday.
Monday 01:00 is hour 1
Tuesday 01:00 is hour 25
Sunday 23:00 is hour 167
etc.
Ideas??This should be a good start:

DECLARE @.startDate SMALLDATETIME
DECLARE @.endDate SMALLDATETIME

--Set Test value for Thursday Feb 22nd
SET @.endDate = ('02/22/2004 15:21:20')
--Set the start date to first day of the week
SET @.startDate = DATEADD(Day, -(DATEPART(WeekDay, @.endDate) - 1), @.endDate)
--Remove the time component of the start date
SET @.startDate = CONVERT(VARCHAR(10), @.startDate, 101)
--Count the number of hours from start of week (Mon) to @.endDate
SELECT DATEDIFF(Hour, @.startDate, @.endDate)

This could be converted to run as part of a select where @.endDate is provided by a column with Date data. If you use this verbatim you will want to test boundary conditions to assure they meet your requirements and I'm sure it could be optimized.

The key is in the setting of @.startDate [DATEADD(Day, -(DATEPART(WeekDay, @.endDate) - 1), @.endDate)].
Evaluation:
DATEPART(WeekDay, @.EndDate) = Day Code for Thursday (4)
(4) - 1 = 3*
-(3) = -3
DATEADD(Day, (-3), @.endDate) = Sets Date To Monday (*determined by the 1 subtracted from the original day code)|||One more try...

(24 * DatePart("dw",'2004-03-8 1:00:00')-1) + {fn Hour('2004-03-8 1:00:00')}

Is it correct?|||You need another set of parenthesis for proper order of operations:

declare @.TimeStamp datetime
set @.TimeStamp = getdate()
select (24 * (DatePart(dw,@.TimeStamp)-1)) + {fn Hour(@.TimeStamp)}|||set datefirst 1
declare @.timestamp datetime
set @.timestamp = getdate()
select (datepart(dd,@.timestamp)-1)*24+datepart(hh,@.timestamp)|||Just an observation that may not affect you, but there aren't always 168 hours in the week. If you observe Daylight Savings Time (http://webexhibits.org/daylightsaving/) then one week has 167 and one has 169 each year. If you need to keep Sidereal (http://tycho.usno.navy.mil/sidereal.html) time, then a different week each year can have a smidgeon more or less than 168.

These don't affect everyone, but they are the basis behind my always using date functions instead of "roll your own" functions like these. I'm not exactly sure how I'd handle this case, but I just wanted to raise the issue before folks go merrily trooping off with a solution that might not always work for them.

-PatP|||declare @.dt datetime
set @.dt = '20040309 1:00'
select DATEDIFF(hh,DATEADD(d,-(DATEPART(w,@.dt)+@.@.DATEFIRST-2)%7,LEFT(@.dt,13)),@.dt)

Hans.|||Just like skinning a cat, there is more than one way to do this and whatever you choose, the result is not very attractive.

how to return a value in a stored procedure?

Hi,
i use a stored procedure for my asp.net application which must return a
value: (number of items). How can i do that?
I tried this but don't know how to giive te found value back.
Thansk
Chris
ALTER PROCEDURE [dbo].[mysp]
AS
declare @.returnValue int
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
select COUNT(DISTINCT numberofitems) from items
END
set @.returnValue = ?
return @.returnValueYou need to use an output parameter. Have a look at these:
http://msdn2.microsoft.com/en-us/library/ms971497.aspx
http://www.sommarskog.se/share_data.html
http://www.informit.com/articles/ar...8&seqNum=9&rl=1
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Chris" <ch@.spam.it> wrote in message
news:%23TUm6byIIHA.5860@.TK2MSFTNGP04.phx.gbl...
> Hi,
> i use a stored procedure for my asp.net application which must return a
> value: (number of items). How can i do that?
> I tried this but don't know how to giive te found value back.
> Thansk
> Chris
>
> ALTER PROCEDURE [dbo].[mysp]
> AS
> declare @.returnValue int
> BEGIN
> -- SET NOCOUNT ON added to prevent extra result sets from
> -- interfering with SELECT statements.
> SET NOCOUNT ON;
> -- Insert statements for procedure here
> select COUNT(DISTINCT numberofitems) from items
> END
> set @.returnValue = ?
> return @.returnValue
>|||Thanks
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> schreef in bericht
news:erDK$IzIIHA.4712@.TK2MSFTNGP04.phx.gbl...
> You need to use an output parameter. Have a look at these:
> http://msdn2.microsoft.com/en-us/library/ms971497.aspx
> http://www.sommarskog.se/share_data.html
> http://www.informit.com/articles/ar...8&seqNum=9&rl=1
>
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Chris" <ch@.spam.it> wrote in message
> news:%23TUm6byIIHA.5860@.TK2MSFTNGP04.phx.gbl...
>|||This is an example I just posted to my book's "Ask Answer" support site. It
calls a SP that returns the Identity value as a Return value. No, you don't
need an output parameter for this--the Return will work fine if you can pass
back an Int.
/****** Object: Stored Procedure dbo.Author_Insert Script Date: 12/7/98
5:08:41 PM ******/
ALTER Procedure Author_Insert @.Author varchar(50), @.Year_Born smallint
As
INSERT INTO Authors
( Author, Year_Born)
VALUES ( @.Author, @.Year_born )
RETURN SCOPE_IDENTITY()
'Copyright (c) 2007 Beta V Corporation. All rights reserved.
' For demonstration purposes only. No warranty of any kind expressed or
implied.
Imports System.Data.SqlClient
Public Class Form1
Dim cn As SqlConnection
Dim cmd As SqlCommand
Sub New()
InitializeComponent()
cn = New SqlConnection(My.Settings.BiblioConnection)
End Sub
Private Sub btnInsert_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnInsert.Click
Dim intRa, intIdentity As Integer
Try
cmd = New SqlCommand("Author_Insert", cn)
With cmd
.CommandType = CommandType.StoredProcedure
.Parameters.AddWithValue("@.Author", tbAuthor.Text)
.Parameters.AddWithValue("@.Year_Born", tbYearBorn.Text)
.Parameters.Add("@.ReturnValue", SqlDbType.Int) _
.Direction = ParameterDirection.ReturnValue ' To capture
RETURN value
cn.Open()
intRa = .ExecuteNonQuery
intIdentity = CInt(.Parameters("@.ReturnValue").Value)
End With
If intRa = 1 Then
MsgBox(String.Format("Row inserted. Identity value {0}", _
intIdentity), MsgBoxStyle.Exclamation)
Else
MsgBox("Insert failed")
End If
Catch exsql As SqlException
MessageBox.Show(exsql.ToString)
Catch ex As Exception
Debug.Assert(False, ex.ToString)
Finally
cn.Close()
End Try
End Sub
End Class
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
----
---
"Chris" <ch@.spam.it> wrote in message
news:%23TUm6byIIHA.5860@.TK2MSFTNGP04.phx.gbl...
> Hi,
> i use a stored procedure for my asp.net application which must return a
> value: (number of items). How can i do that?
> I tried this but don't know how to giive te found value back.
> Thansk
> Chris
>
> ALTER PROCEDURE [dbo].[mysp]
> AS
> declare @.returnValue int
> BEGIN
> -- SET NOCOUNT ON added to prevent extra result sets from
> -- interfering with SELECT statements.
> SET NOCOUNT ON;
> -- Insert statements for procedure here
> select COUNT(DISTINCT numberofitems) from items
> END
> set @.returnValue = ?
> return @.returnValue
>

How to return a value if recordcount = 0

How would I return a select statement if the records returned is 0
i.e
Select * from orders where employeeID = 0
if @.RowCount = 0
--Return Shipname as 'None'
Select 'None' = shipname ect,
Thanks
Stephen K. Miyasato
MDsyncHi Stephen,
I would do something like...
SELECT * FROM Orders where EmployeeID = 0
IF @.@.ROWCOUNT = 0
SELECT 'None' As ShipName
or
IF EXISTS (SELECT * FROM Orders Where EmployeeID = 0)
SELECT * FROM Orders WHERE EmployeeID = 0
ELSE
SELECT 'None' As ShipName
Both examples would return a single row with a single column called
[ShipName] with the value 'None' in the event that no rows exist with an
employeeID of 0.
Rich.
"Stephen K. Miyasato" <miyasat@.flex.com> wrote in message
news:OWMcSE6cGHA.564@.TK2MSFTNGP02.phx.gbl...
> How would I return a select statement if the records returned is 0
> i.e
> Select * from orders where employeeID = 0
> if @.RowCount = 0
> --Return Shipname as 'None'
> Select 'None' = shipname ect,
>
> Thanks
> Stephen K. Miyasato
> MDsync
>|||Try this:
Select * from orders where employeeID = 0
if @.@.RowCount = 0
begin
--Return Shipname as 'None'
Select 'None' as 'shipname'
end|||Thanks but I could not get this to work.
Here is the actual query:
The query as it now is return two sets. The first set has 0 rows, the next
set is the one I need to produce.
if I use Exist(Select ..), I get and error since I have NTEXT values and
order by is not allowed.
Any other suggustions?
Stephen K. Miyasato
/****** Object: Table [dbo].[orderTest] Script Date: 5/9/2006 12:42:23
PM ******/
if not exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[orderTest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [orderTest] (
[PatNo] [int] NULL ,
[Name] [varchar] (24) NULL ,
[Dosage] [varchar] (25) NULL ,
[Sig] [varchar] (30) NULL ,
[PatSig] [varchar] (50) NULL ,
[RxNo] [int] NULL ,
[Breakfast] [varchar] (1) NULL ,
[Lunch] [varchar] (1) NULL ,
[Dinner] [varchar] (1) NULL ,
[QHS] [varchar] (1) NULL ,
[Comments] [varchar] (60) NULL ,
[QuantityL] [varchar] (20) NULL ,
[Quantity] [float] NULL ,
[Generic] [varchar] (60) NULL ,
[BrandName] [varchar] (60) NULL ,
[MDrx] [int] NULL ,
[Category_id] [int] NULL ,
[Daily] [smallint] NULL ,
[Memo] [ntext] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END
GO
Declare @.PatNo int
Set @.PatNo = 7160
SELECT PatNo, Name, Dosage, Sig, PatSig, RxNo, Breakfast, Lunch,
Dinner, QHS,
Comments, QuantityL, Quantity, Generic, BrandName,
MDrx, Category_id, Daily,
Memo
FROM orderTest
WHERE (PatNo = @.PatNo) AND (StopDate IS NULL)
ORDER BY Daily
if @.@.RowCount = 0
SELECT @.PatNo AS PatNo,Null as Name, Null as Dosage, Null as Sig, Null
as PatSig, Null as RxNo, Null as Breakfast, Null as Lunch, Null as
Dinner,Null as QHS,
Null as Comments,Null as QuantityL, Null as Quantity,
Null as Generic, 'None' AS BrandName, Null as MDrx, Null as Category_id,
Null as Daily,
Null as Memo
"Richard O'Brien" <groups@.rjoconsulting.co.uk> wrote in message
news:ecp6gH6cGHA.4912@.TK2MSFTNGP05.phx.gbl...
> Hi Stephen,
> I would do something like...
> SELECT * FROM Orders where EmployeeID = 0
> IF @.@.ROWCOUNT = 0
> SELECT 'None' As ShipName
> or
> IF EXISTS (SELECT * FROM Orders Where EmployeeID = 0)
> SELECT * FROM Orders WHERE EmployeeID = 0
> ELSE
> SELECT 'None' As ShipName
> Both examples would return a single row with a single column called
> [ShipName] with the value 'None' in the event that no rows exist with an
> employeeID of 0.
> Rich.
> "Stephen K. Miyasato" <miyasat@.flex.com> wrote in message
> news:OWMcSE6cGHA.564@.TK2MSFTNGP02.phx.gbl...
>|||Stephen,
Select * from orders where employeeID = 0
union all
select
-- whatever values you want
1,2,3,4,...
where not exists(Select 1 from orders where employeeID = 0)|||StopDate isn't a column in the table definition you gave.
Try this.
Declare @.PatNo int
Set @.PatNo = 7160
if exists (select * from ordertest where (PatNo = @.PatNo)) -- AND
(StopDate IS NULL))
begin
SELECT PatNo, Name, Dosage, Sig, PatSig, RxNo, Breakfast, Lunch,
Dinner, QHS,
Comments, QuantityL, Quantity, Generic,
BrandName,
MDrx, Category_id, Daily,
Memo
FROM orderTest
WHERE (PatNo = @.PatNo)-- AND (StopDate IS NULL)
ORDER BY Daily
end
else begin
SELECT @.PatNo AS PatNo,Null as Name, Null as Dosage, Null as Sig,
Null
as PatSig, Null as RxNo, Null as Breakfast, Null as Lunch, Null as
Dinner,Null as QHS,
Null as Comments,Null as QuantityL, Null as
Quantity,
Null as Generic, 'None' AS BrandName, Null as MDrx, Null as
Category_id,
Null as Daily,
Null as Memo
end

How to return a value from Stored Procedure?

Hi,
I am calling a stored procedure from my Java script app and trying to use
the returned value later on in my app.
Here is the javascript code.
var connObj = Server.CreateObject("ADODB.connection");
connObj.Open("DRIVER={ SQL Server }; SERVER = 9.1.2.1;
DATABASE=TEST","sa","xyz");
var recordSetObj = Server.CreateObject("ADODB.Recordset");
var StrName = "venkat";
var RetVal;
var query = "Exec ProcSP '"+StrName+'", '"+RetVal+"'";
recordSetObj.Open(query,connObj);
Response.Write("Return Value= "+RetVal);
/******Stored Procedure********/
Create Procedure ProcSP
@.StrName nvarchar(10),
@.RetVal integer OUTPUT
AS
SET RetVal = 1
return
GO
The value returned is empty, even if I initialize RetVal to 2 say before the
call of Store procedure then once the execution of stored procedure is
finished it prints as 2 only. It is not getting changed to 1.
Please note the stored procedure is working fine if i execute any queries, i
mean the stored procedure is getting called from the app and is working
properly the only thing is i am not able to return any value from it.
I greatly appreciate anyhelp on this, i need to fix this issue ASAP, i am
running short of time. Please help me.
Regards,
VenkatHave you tried using parameters and the command object?
That's typically how you retrieve the values of output
parameters and return values in ADO.
-Sue
On Fri, 28 Nov 2003 11:09:31 +0530, "Venkat"
<venkat_kp@.yahoo.com> wrote:

quote:

---

>Hi,
>I am calling a stored procedure from my Java script app and trying to use
>the returned value later on in my app.
>Here is the javascript code.
>var connObj = Server.CreateObject("ADODB.connection");
>connObj.Open("DRIVER={ SQL Server }; SERVER = 9.1.2.1;
>DATABASE=TEST","sa","xyz");
>var recordSetObj = Server.CreateObject("ADODB.Recordset");
>var StrName = "venkat";
>var RetVal;
>var query = "Exec ProcSP '"+StrName+'", '"+RetVal+"'";
>recordSetObj.Open(query,connObj);
>Response.Write("Return Value= "+RetVal);
>
>/******Stored Procedure********/
>Create Procedure ProcSP
>@.StrName nvarchar(10),
>@.RetVal integer OUTPUT
>AS
>SET RetVal = 1
>return
>GO
>
>The value returned is empty, even if I initialize RetVal to 2 say before th
e
>call of Store procedure then once the execution of stored procedure is
>finished it prints as 2 only. It is not getting changed to 1.
>Please note the stored procedure is working fine if i execute any queries,
i
>mean the stored procedure is getting called from the app and is working
>properly the only thing is i am not able to return any value from it.
>
>I greatly appreciate anyhelp on this, i need to fix this issue ASAP, i am
>running short of time. Please help me.
>
>Regards,
>Venkat
>
>
>

---

How To return a value from stored procedure when select nothing

Hi, nice to meet you all. I'm new developer and need help. Normally i use "sqldatasource1.select()", but need to fill argument, what is argument? Actually the main point i wan to return a value from stored procedure or got any methods can do this way? Hope you all can help me... Thank

U can use out parameter to get the value. Plz check out

Thank u

Baba

Please remember to click "Mark as Answer" on this post if it helped you.

|||

Thank you for your reply, but i already try this way, can't work because it need argument... do u have any sample or any good idea to get the parameter? Thank You

|||

Hi chohoo,

sqldatasource.select() method need aDataSourceSelectArguments object as a parameter. In your case you can create a empty instance and pass into select method. like this:

DataSourceSelectArguments args=new DataSourceSelectArguments();

sqldatasource1.select(args);

You can use a dataview object to hold the data returned by select().

BTW, " retrun a value from a stored procedure" is another story. Does that has anything to do with sqldatasource.select?

|||

Thank You for Ur Reply, actually I already used other method (Use dataset read to return). Whatever Thank You Becoz You Help Me.

How to return a value from SP

Hi all,

How to return a value from a store procedure?

I use a VBA to call a store procedure, but I would like to be able to return the result back to a variable.

Here is an VBA example:

Dim GetDestinationID As Long

Dim conConnection As ADODB.Connection
Dim StrSQL As String

Set conConnection = CurrentProject.Connection

StrSQL = "usp_GetDestinationID " & 2 & ", " & _
GetDestinationID

conConnection.Execute StrSQL, iAffected, adExecuteNoRecords

I would like to return GetDestinationID.

Here is the SP:

CREATE PROCEDURE [dbo].[usp_GetDestinationID]

(
@.intOrderID int,
@.intDestinationID int=0 OUTPUT

)

AS

BEGIN

set @.intDestinationID=(SELECT lv.DestinationID
FROM [Land Voyages] AS lv INNER JOIN [Pickup Booking List] AS pbl
ON lv.LandVoyageID=pbl.LandVoyageID
WHERE pbl.OrderID = @.intOrderID)

END
GO

What is wrong? Can I return a value to a Visual Basic Application from a Store Procedure?

Regardshttp://dbforums.com/t916861.html|||HI all

I found a solution to my question about VBA calling a Stored Procedure and returning a value

VBA:
'-------------------
Private Function GetDestinationID(OrderID As Long) As Long
On Error GoTo GetDestinationID_Err

Dim cmd As ADODB.Command
Set cmd = New ADODB.Command

With cmd

.ActiveConnection = CurrentProject.Connection
.CommandText = "usp_GetDestinationID"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("@.intOrderID", adInteger, adParamInput, , OrderID)
.Parameters.Append .CreateParameter("@.intDestinationID", adInteger, adParamOutput)
.Execute
GetDestinationID = .Parameters("@.intDestinationID").Value
End With

WrapUp:


Exit_GetDestinationID:
Set cmd = Nothing
Exit Function

GetDestinationID_Err:
Call LogMsgError(Err.Description, Err.Number, ModuleName$, "GetDestinationID")
Resume Exit_GetDestinationID
End Function

'----------------
T-SQL:

CREATE PROCEDURE dbo.usp_GetDestinationID

(
@.intOrderID int,
@.intDestinationID int=0 OUTPUT
)

AS

SET NOCOUNT ON

BEGIN

SELECT @.intDestinationID=lv.DestinationID
FROM dbo.[Land Voyages] AS lv INNER JOIN dbo.[Pickup Booking List] AS pbl
ON lv.LandVoyageID=pbl.LandVoyageID
WHERE pbl.OrderID = @.intOrderID

END
GO
'-----------

Thanks to Igor for suggestions.

Dani

How To Return a Static Value?

I have the following stored procedure...

CREATE Procedure UserGetInfo2
(
@.UserID int, @.SystemTimePeriodID int
)
As
set nocount on
SELECT Users.UserId as UserID, Users.UserName as UserName, Users.RealName as RealName, UserTimePeriod.BudgetCode as BudgetCode, UserTimePeriod.SystemTimePeriodID as SystemTimePeriodID, Users.Password as Password, Users.SSN as SSN, Users.Location as Location, Users.ScheduleType as ScheduleType, Users.EmployeeType as EmployeeType, Users.TimeAccounted as TimeAccounted
FROM Users INNER JOIN UserTimePeriod ON Users.UserId = UserTimePeriod.UserID
WHERE (users.userID= @.UserID) AND (UserTimePeriod.SystemTimePeriodID = @.SystemTimePeriodID)
return
GO

The problem lies in that when a person has a SystemTimePeriodID over a certain value, there is no UserTimePeriod record since it has not been created yet.

Obviously, I need to wrap this in an IF...EXISTS

IF EXISTS (SELECT UserTimePeriodID FROM UserTimePeriod WHERE (SystemTimePeriodID = @.SystemTimePeriodID) AND (UserID = @.UserID))

(the SELECT above, since that's what needs to come back if the data exists)

ELSE

Do the same select but put in a static value for BudgetCode, like '0000'

GO

How could I do the part where the IF...EXISTS fails?

I'm... not sure I can use RETURNS, since it feeds into this recordset:

rstUserInfo2.Open "UserGetInfo2 " & Request("UserID") & ", " & Request("SYSTIMEPERIODID")

and later uses values from that RecordSet, such as <td><%=rstUserInfo("BudgetCode") & ""%></td>

Hi friend, I dont know if I understand you in the right way. Try this:

CREATE Procedure UserGetInfo2 (@.UserIDint, @.SystemTimePeriodIDint )As set nocount onreturnGOIFNOT EXISTS (SELECT UserTimePeriodIDFROM UserTimePeriodWHERE (SystemTimePeriodID = @.SystemTimePeriodID)AND (UserID = @.UserID))SET @.SystemTimePeriodID = 0;SELECT Users.UserIdas UserID, Users.UserNameas UserName, Users.RealNameas RealName, UserTimePeriod.BudgetCodeas BudgetCode, UserTimePeriod.SystemTimePeriodIDas SystemTimePeriodID, Users.Passwordas Password, Users.SSNas SSN, Users.Locationas Location, Users.ScheduleTypeas ScheduleType, Users.EmployeeTypeas EmployeeType, Users.TimeAccountedas TimeAccountedFROM UsersINNERJOIN UserTimePeriodON Users.UserId = UserTimePeriod.UserIDWHERE (users.userID= @.UserID)AND (UserTimePeriod.SystemTimePeriodID = @.SystemTimePeriodID)

Hope this helps.

|||No, I would need to set the outgoing BudgetCode to something, not the incoming SystemTimePeriodID.|||

It's as easy as:

Select..... , '0000' as BudgetCode, ...

|||I knew I had seen that done somewhere. Thank you for clearing up my brain's log jam.

How to return a data list from the attributes on xml column?

I have a table which contain a xml column.
Currently, I use for-each-row strategy to store xml value apply OPENXML
function to scan xml-row.
It is very very spend-time when there are many rows on table.
Is there any another method?Hi,
Do you mean that the table already has an SQL Server 2005 native XML in it?
You can always (even in SQL Server 2000) use SELECT ... FOR XML to store
rowset information in XML format, without using a for-each-row strategy. SQL
Server 2005 has many enhancements to SELECT... FOR XML, including the
ability to store rowset information as a native XML SQL Server data types.
In SQL Server 2005, the XML data type's nodes() method can be used instead
of OPENXML. It's usually quicker than OPENXML and takes less memory than
OPENXML to process.
Hope this helps,
Bob Beauchemin
http://www.SQLskills.com/blogs/bobb
"ABC" <abc@.abc.com> wrote in message
news:O4l40pEPHHA.5000@.TK2MSFTNGP03.phx.gbl...
>I have a table which contain a xml column.
> Currently, I use for-each-row strategy to store xml value apply OPENXML
> function to scan xml-row.
> It is very very spend-time when there are many rows on table.
> Is there any another method?
>
>

How to return a data list from the attributes on xml column?

I have a table which contain a xml column.
Currently, I use for-each-row strategy to store xml value apply OPENXML
function to scan xml-row.
It is very very spend-time when there are many rows on table.
Is there any another method?
Hi,
Do you mean that the table already has an SQL Server 2005 native XML in it?
You can always (even in SQL Server 2000) use SELECT ... FOR XML to store
rowset information in XML format, without using a for-each-row strategy. SQL
Server 2005 has many enhancements to SELECT... FOR XML, including the
ability to store rowset information as a native XML SQL Server data types.
In SQL Server 2005, the XML data type's nodes() method can be used instead
of OPENXML. It's usually quicker than OPENXML and takes less memory than
OPENXML to process.
Hope this helps,
Bob Beauchemin
http://www.SQLskills.com/blogs/bobb
"ABC" <abc@.abc.com> wrote in message
news:O4l40pEPHHA.5000@.TK2MSFTNGP03.phx.gbl...
>I have a table which contain a xml column.
> Currently, I use for-each-row strategy to store xml value apply OPENXML
> function to scan xml-row.
> It is very very spend-time when there are many rows on table.
> Is there any another method?
>
>

How to return 0 instead of null when using a sum function?

Hi,

I basically do not want to return a null value as a result of using a sum function (using sum against 0 rows).

Is there a common way to avoid this?

Thanx

Interesting you should ask. I was looking at the same problemyesterday. My results are displayed in a DataGrid and thankfully itdoesn't barf on the NULL values. It displays -1 instead. Of coursethat's still not desirable as the correct answer for SUM(nothing)should be 0 IMO.
Let's hope someone has a suggestion.
|||You could use the ISNULL function to calculate a 0 whenever the SUM contains a NULL:

SELECT ISNULL(SUM(myColumn,0)) FROM myTable WHERE 0=1

|||

Or, as a slight variation, use ISNULL to replace the result of the SUM for instances where no rows result in an answer of NULL:

SELECT ISNULL(SUM(myColumn),0) FROM myTable WHERE 0 = 1

(Note the different parenthesis placement: tmorton's will replace any NULL value with zero BEFORE aggregation; mine will replace theentire result with 0 AFTER aggregation in the event of a NULL sum).

|||

pjmcb wrote:

Or, as a slight variation, use ISNULL to replace the result of the SUM for instances where no rows result in an answer of NULL:
SELECT ISNULL(SUM(myColumn),0) FROM myTable WHERE 0 = 1
(Note the different parenthesis placement: tmorton's will replace anyNULL value with zero BEFORE aggregation; mine will replace the entireresult with 0 AFTER aggregation in the event of a NULL sum).

Thank you for catching my typo pjmcb! What I actually posted is not even syntactically correct. :-)
I should have copied-and-pasted from Query Analyzer instead of retyping.



|||I didn't think you could do that, but I was too lazy to open up QA to test it out...

how to retrive the value of "@DerivedFileName"

SqlCommand sqlCommand =newSqlCommand(spName, Conn);

sqlCommand.CommandType =CommandType.StoredProcedure;

sqlCommand.Parameters.Add("@.XMLString",SqlDbType.Xml).Value = XMLstring;

sqlCommand.Parameters["@.XMLString"].Direction =ParameterDirection.Input;

sqlCommand.Parameters.Add("@.DerivedFileName",SqlDbType.VarChar,50);

sqlCommand.Parameters["@.DerivedFileName"].Direction =ParameterDirection.Output;

int test = sqlCommand.ExecuteNonQuery();

Use

str = sqlCommand.Parameters["@.DerivedFileName"].Value.ToString();