Returning/storing a value for use in a stored proc.

Hi:
I have a stored procedure which performs an insert statement. At the
conclusion of the insert, I have SELECT SCOPE_IDENTITY(). Now, I want to be
able to use this value for the next stored procedure (which will then perfor
m
an update on the newly inserted record). Here's an example:
--Step #1:
Execute spCCD @.COMPANY_ID
--which looks like this:
CREATE PROCEDURE [dbo].[spSTMNT_CCDPPD]
@.Company_ID
Declare @.STMNT_ID int
AS
Insert Into STMNT (
COMPANY_ID,
Tran_Fee1,
Tran_Fee2
)
Values (
@.COMPANY_ID,
0.15,
0.25
)
Set @.STMNT_ID = (SELECT SCOPE_IDENTITY())
--Step #2
Execute spVoid @.STMNT_ID
---
So calling the stored procedures will look like this:
Execute spSTMNT_CCDPPD @.COMPANY_ID --this will return @.STMNT_ID
Execute spVoid @.STMNT_IDEric,
Use an output parameter.
CREATE PROCEDURE [dbo].[spSTMNT_CCDPPD]
@.Company_ID,
@.STMNT_ID int output
AS
set nocount on
Insert Into STMNT (
COMPANY_ID,
Tran_Fee1,
Tran_Fee2
)
Values (
@.COMPANY_ID,
0.15,
0.25
)
Set @.STMNT_ID = SCOPE_IDENTITY()
go
declare @.STMNT_ID int
Execute spSTMNT_CCDPPD @.COMPANY_ID, @.STMNT_ID output
Execute spVoid @.STMNT_ID
go
Remember to handle errors.
AMB
"Eric" wrote:

> Hi:
> I have a stored procedure which performs an insert statement. At the
> conclusion of the insert, I have SELECT SCOPE_IDENTITY(). Now, I want to
be
> able to use this value for the next stored procedure (which will then perf
orm
> an update on the newly inserted record). Here's an example:
> --Step #1:
> Execute spCCD @.COMPANY_ID
> --which looks like this:
> CREATE PROCEDURE [dbo].[spSTMNT_CCDPPD]
> @.Company_ID
> Declare @.STMNT_ID int
> AS
> Insert Into STMNT (
> COMPANY_ID,
> Tran_Fee1,
> Tran_Fee2
> )
> Values (
> @.COMPANY_ID,
> 0.15,
> 0.25
> )
> Set @.STMNT_ID = (SELECT SCOPE_IDENTITY())
> --Step #2
> Execute spVoid @.STMNT_ID
> ---
> So calling the stored procedures will look like this:
> Execute spSTMNT_CCDPPD @.COMPANY_ID --this will return @.STMNT_ID
> Execute spVoid @.STMNT_ID
>|||Correction,

> @.Company_ID,
CREATE PROCEDURE [dbo].[spSTMNT_CCDPPD]
@.Company_ID int,
@.STMNT_ID int output
AS
set nocount on
Insert Into STMNT (
COMPANY_ID,
Tran_Fee1,
Tran_Fee2
)
Values (
@.COMPANY_ID,
0.15,
0.25
)
Set @.STMNT_ID = SCOPE_IDENTITY()
go
declare @.COMPANY_ID int
declare @.STMNT_ID int
set @.COMPANY_ID = 123
Execute spSTMNT_CCDPPD @.COMPANY_ID, @.STMNT_ID output
Execute spVoid @.STMNT_ID
go
AMB
"Alejandro Mesa" wrote:
> Eric,
> Use an output parameter.
> CREATE PROCEDURE [dbo].[spSTMNT_CCDPPD]
> @.Company_ID,
> @.STMNT_ID int output
> AS
> set nocount on
> Insert Into STMNT (
> COMPANY_ID,
> Tran_Fee1,
> Tran_Fee2
> )
> Values (
> @.COMPANY_ID,
> 0.15,
> 0.25
> )
> Set @.STMNT_ID = SCOPE_IDENTITY()
> go
> declare @.STMNT_ID int
> Execute spSTMNT_CCDPPD @.COMPANY_ID, @.STMNT_ID output
> Execute spVoid @.STMNT_ID
> go
> Remember to handle errors.
>
> AMB
> "Eric" wrote:
>

Returning XML values

I have an XML field in one of our tables.
A sample of the data is here:
<PackageAddTask xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/
XMLSchema"><Item><PackageItemId>11</
PackageItemId><PackageId>6</PackageId><Path>https://10.126.22.1/
SBGImages</Path><Filename>image1.img</Filename><InstallOrder>0</
InstallOrder></Item></PackageAddTask>
I need to retrieve the PackageID value from this field
This query returns the entire item.
SELECT Detail.query('/PackageAddTask/Item/PackageId')
FROM Task T Where TaskTypeID = 4
This query SHOULD return the value, but instead, return NULL
Select Detail.value('(/PackageAddTask/Item/@.PackageItemId)[1]',
'int')
as Result
FROM Task T Where TaskTypeID = 4
What am I missing here?Brian Bunin,
Try:
Select Detail.value('(/PackageAddTask/Item/PackageItemId)[1]', 'int') as
Result
FROM Task T
Where TaskTypeID = 4
go
AMB
"Brian Bunin" wrote:
> I have an XML field in one of our tables.
> A sample of the data is here:
> <PackageAddTask xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
> xmlns:xsd="http://www.w3.org/2001/
> XMLSchema"><Item><PackageItemId>11</
> PackageItemId><PackageId>6</PackageId><Path>https://10.126.22.1/
> SBGImages</Path><Filename>image1.img</Filename><InstallOrder>0</
> InstallOrder></Item></PackageAddTask>
> I need to retrieve the PackageID value from this field
>
> This query returns the entire item.
> SELECT Detail.query('/PackageAddTask/Item/PackageId')
> FROM Task T Where TaskTypeID = 4
>
> This query SHOULD return the value, but instead, return NULL
> Select Detail.value('(/PackageAddTask/Item/@.PackageItemId)[1]',
> 'int')
> as Result
> FROM Task T Where TaskTypeID = 4
>
> What am I missing here?
>

Returning XML values

I have an XML field in one of our tables.
A sample of the data is here:
<PackageAddTask xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/
XMLSchema"><Item><PackageItemId>11</
PackageItemId><PackageId>6</PackageId><Path>https://10.126.22.1/
SBGImages</Path><Filename>image1.img</Filename><InstallOrder>0</
InstallOrder></Item></PackageAddTask>
I need to retrieve the PackageID value from this field
This query returns the entire item.
SELECT Detail.query('/PackageAddTask/Item/PackageId')
FROM Task T Where TaskTypeID = 4
This query SHOULD return the value, but instead, return NULL
Select Detail.value('(/PackageAddTask/Item/@.PackageItemId)[1]',
'int')
as Result
FROM Task T Where TaskTypeID = 4
What am I missing here?Brian Bunin,
Try:
Select Detail.value('(/PackageAddTask/Item/PackageItemId)[1]', 'int') as
Result
FROM Task T
Where TaskTypeID = 4
go
AMB
"Brian Bunin" wrote:

> I have an XML field in one of our tables.
> A sample of the data is here:
> <PackageAddTask xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
> xmlns:xsd="http://www.w3.org/2001/
> XMLSchema"><Item><PackageItemId>11</
> PackageItemId><PackageId>6</PackageId><Path>https://10.126.22.1/
> SBGImages</Path><Filename>image1.img</Filename><InstallOrder>0</
> InstallOrder></Item></PackageAddTask>
> I need to retrieve the PackageID value from this field
>
> This query returns the entire item.
> SELECT Detail.query('/PackageAddTask/Item/PackageId')
> FROM Task T Where TaskTypeID = 4
>
> This query SHOULD return the value, but instead, return NULL
> Select Detail.value('(/PackageAddTask/Item/@.PackageItemId)[1]',
> 'int')
> as Result
> FROM Task T Where TaskTypeID = 4
>
> What am I missing here?
>

returning xml from function

I have a sproc I am trying to convert to a function.
The sproc works fine the sproc is giving me errors.
I am not sure if the With or the XML datatype is the problem
Msg 156, Level 15, State 1, Procedure SurveyResultsXML, Line 13
Incorrect syntax near the keyword 'with'.
Msg 319, Level 15, State 1, Procedure SurveyResultsXML, Line 13
Incorrect syntax near the keyword 'with'. If this statement is a common
table expression or an xmlnamespaces clause, the previous statement must be
terminated with a semicolon.
Msg 102, Level 15, State 1, Procedure SurveyResultsXML, Line 16
Incorrect syntax near ','.
Msg 102, Level 15, State 1, Procedure SurveyResultsXML, Line 39
Incorrect syntax near ')'.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
alter FUNCTION [dbo].[SurveyResultsXML]
(@.SurveyID int)
RETURNS XML
AS
BEGIN
declare @.result XML;
set @.result=(
with
question ( QuestionID ) as
(Select distinct QuestionID From SurveyResults Where
SurveyID=@.SurveyID),
results ( QuestionID, Answer, AnswerCount) as
(Select QuestionID, Answer, Count(Answer)
From SurveyResults
Where SurveyID=@.SurveyID
group by questionid, answer)
select SurveyName, SurveyQuestions,
(
Select question.QuestionID, results.Answer, results.AnswerCount
From question join results
on question.questionid =results.questionid
for xml auto, Root('QuestionResults'), type
)
from SurveyProfiles Join
Surveys on SurveyProfiles.SurveyProfileID = Surveys.SurveyProfileID
WHERE SurveyID=@.SurveyID
for xml Raw('Results'), Elements
);
return @.result ;
END
Hi Chunk,
I understand that your SQL statement including CTE failed to be executed
with the error messages.
If I have misunderstood, please let me know.
The problem is that CTE statement cannot be used as a right value of SET
expression. I tried to use a temp table to store the query result; however
since temp table is not supported in a function, I must use a stored
procedure to work it. Please refer to:
CREATE PROCEDURE [dbo].[SurveyResultsXML]
(@.SurveyID int,
@.result xml output)
AS
BEGIN
with
question ( QuestionID ) as
(Select distinct QuestionID From SurveyResults Where
SurveyID=@.SurveyID),
results ( QuestionID, Answer, AnswerCount) as
(Select QuestionID, Answer, Count(Answer)
From SurveyResults
Where SurveyID=@.SurveyID
group by questionid, answer)
select SurveyName, SurveyQuestions,
(
Select question.QuestionID, results.Answer, results.AnswerCount
From question join results
on question.questionid =results.questionid
for xml auto, Root('QuestionResults'), type
) into #TempResultTable
from SurveyProfiles Join
Surveys on SurveyProfiles.SurveyProfileID = Surveys.SurveyProfileID
WHERE SurveyID=@.SurveyID
set @.result = (
select * from #TempResultTable
for xml Raw('Results'), Elements
)
drop table #TempResultTable
END
I think that using SP is a better way; but I am not sure if you have to use
a function in your situation.
Actually I can use a view to wrap the CTE express by removing "WHERE
SurveyID=@.SurveyID", then use a function to query from the view like this:
CREATE VIEW v_getSurveyQuestions
AS
with
question ( QuestionID ) as
(Select distinct SurveyID, QuestionID From SurveyResults),
results ( QuestionID, Answer, AnswerCount) as
(Select QuestionID, Answer, Count(Answer)
From SurveyResults
group by questionid, answer)
select SurveyName, SurveyQuestions, SurveyID,
(
Select question.QuestionID, results.Answer, results.AnswerCount
From question join results
on question.questionid =results.questionid
for xml auto, Root('QuestionResults'), type
)
from SurveyProfiles Join
Surveys on SurveyProfiles.SurveyProfileID = Surveys.SurveyProfileID
CREATE FUNCTION ufn_getSurveyQuestionsByID
( @.surveyId int)
returns xml
AS
BEGIN
declare @.result xml
set @.result = (
select * from v_getSurveyQuestions where SurveyID=@.surveyId
for xml Raw('Results'), Elements
)
return @.result
END
The problem is that if your tables have large amount of data, the
performance may be very poor. I recommend that you use the first method.
Hope this helps. Please feel free to let me know if you have any other
questions or concerns.
Charles Wang
Microsoft Online Community Support
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
|||thanks, it worked great.
"Charles Wang[MSFT]" wrote:

> Hi Chunk,
> I understand that your SQL statement including CTE failed to be executed
> with the error messages.
> If I have misunderstood, please let me know.
> The problem is that CTE statement cannot be used as a right value of SET
> expression. I tried to use a temp table to store the query result; however
> since temp table is not supported in a function, I must use a stored
> procedure to work it. Please refer to:
> CREATE PROCEDURE [dbo].[SurveyResultsXML]
> (@.SurveyID int,
> @.result xml output)
> AS
> BEGIN
> with
> question ( QuestionID ) as
> (Select distinct QuestionID From SurveyResults Where
> SurveyID=@.SurveyID),
> results ( QuestionID, Answer, AnswerCount) as
> (Select QuestionID, Answer, Count(Answer)
> From SurveyResults
> Where SurveyID=@.SurveyID
> group by questionid, answer)
> select SurveyName, SurveyQuestions,
> (
> Select question.QuestionID, results.Answer, results.AnswerCount
> From question join results
> on question.questionid =results.questionid
> for xml auto, Root('QuestionResults'), type
> ) into #TempResultTable
> from SurveyProfiles Join
> Surveys on SurveyProfiles.SurveyProfileID = Surveys.SurveyProfileID
> WHERE SurveyID=@.SurveyID
> set @.result = (
> select * from #TempResultTable
> for xml Raw('Results'), Elements
> )
> drop table #TempResultTable
> END
> I think that using SP is a better way; but I am not sure if you have to use
> a function in your situation.
> Actually I can use a view to wrap the CTE express by removing "WHERE
> SurveyID=@.SurveyID", then use a function to query from the view like this:
> CREATE VIEW v_getSurveyQuestions
> AS
> with
> question ( QuestionID ) as
> (Select distinct SurveyID, QuestionID From SurveyResults),
> results ( QuestionID, Answer, AnswerCount) as
> (Select QuestionID, Answer, Count(Answer)
> From SurveyResults
> group by questionid, answer)
> select SurveyName, SurveyQuestions, SurveyID,
> (
> Select question.QuestionID, results.Answer, results.AnswerCount
> From question join results
> on question.questionid =results.questionid
> for xml auto, Root('QuestionResults'), type
> )
> from SurveyProfiles Join
> Surveys on SurveyProfiles.SurveyProfileID = Surveys.SurveyProfileID
>
> CREATE FUNCTION ufn_getSurveyQuestionsByID
> ( @.surveyId int)
> returns xml
> AS
> BEGIN
> declare @.result xml
> set @.result = (
> select * from v_getSurveyQuestions where SurveyID=@.surveyId
> for xml Raw('Results'), Elements
> )
> return @.result
> END
> The problem is that if your tables have large amount of data, the
> performance may be very poor. I recommend that you use the first method.
> Hope this helps. Please feel free to let me know if you have any other
> questions or concerns.
> Charles Wang
> Microsoft Online Community Support
> ================================================== ====
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ================================================== ====
> This posting is provided "AS IS" with no warranties, and confers no rights.
> ================================================== ====
>
>
|||Hi,
Appreciate your update and response. I am glad to hear that the suggestions
are helpful. If you have any other questions or concerns, please do not
hesitate to contact us.
Have a nice day!
Charles Wang
Microsoft Online Community Support

returning xml from function

I have a sproc I am trying to convert to a function.
The sproc works fine the sproc is giving me errors.
I am not sure if the With or the XML datatype is the problem
Msg 156, Level 15, State 1, Procedure SurveyResultsXML, Line 13
Incorrect syntax near the keyword 'with'.
Msg 319, Level 15, State 1, Procedure SurveyResultsXML, Line 13
Incorrect syntax near the keyword 'with'. If this statement is a common
table expression or an xmlnamespaces clause, the previous statement must be
terminated with a semicolon.
Msg 102, Level 15, State 1, Procedure SurveyResultsXML, Line 16
Incorrect syntax near ','.
Msg 102, Level 15, State 1, Procedure SurveyResultsXML, Line 39
Incorrect syntax near ')'.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
alter FUNCTION [dbo].[SurveyResultsXML]
(@.SurveyID int)
RETURNS XML
AS
BEGIN
declare @.result XML;
set @.result=(
with
question ( QuestionID ) as
(Select distinct QuestionID From SurveyResults Where
SurveyID=@.SurveyID),
results ( QuestionID, Answer, AnswerCount) as
(Select QuestionID, Answer, Count(Answer)
From SurveyResults
Where SurveyID=@.SurveyID
group by questionid, answer)
select SurveyName, SurveyQuestions,
(
Select question.QuestionID, results.Answer, results.AnswerCount
From question join results
on question.questionid =results.questionid
for xml auto, Root('QuestionResults'), type
)
from SurveyProfiles Join
Surveys on SurveyProfiles.SurveyProfileID = Surveys.SurveyProfileID
WHERE SurveyID=@.SurveyID
for xml Raw('Results'), Elements
);
return @.result ;
ENDHi Chunk,
I understand that your SQL statement including CTE failed to be executed
with the error messages.
If I have misunderstood, please let me know.
The problem is that CTE statement cannot be used as a right value of SET
expression. I tried to use a temp table to store the query result; however
since temp table is not supported in a function, I must use a stored
procedure to work it. Please refer to:
CREATE PROCEDURE [dbo].[SurveyResultsXML]
(@.SurveyID int,
@.result xml output)
AS
BEGIN
with
question ( QuestionID ) as
(Select distinct QuestionID From SurveyResults Where
SurveyID=@.SurveyID),
results ( QuestionID, Answer, AnswerCount) as
(Select QuestionID, Answer, Count(Answer)
From SurveyResults
Where SurveyID=@.SurveyID
group by questionid, answer)
select SurveyName, SurveyQuestions,
(
Select question.QuestionID, results.Answer, results.AnswerCount
From question join results
on question.questionid =results.questionid
for xml auto, Root('QuestionResults'), type
) into #TempResultTable
from SurveyProfiles Join
Surveys on SurveyProfiles.SurveyProfileID = Surveys.SurveyProfileID
WHERE SurveyID=@.SurveyID
set @.result = (
select * from #TempResultTable
for xml Raw('Results'), Elements
)
drop table #TempResultTable
END
I think that using SP is a better way; but I am not sure if you have to use
a function in your situation.
Actually I can use a view to wrap the CTE express by removing "WHERE
SurveyID=@.SurveyID", then use a function to query from the view like this:
CREATE VIEW v_getSurveyQuestions
AS
with
question ( QuestionID ) as
(Select distinct SurveyID, QuestionID From SurveyResults),
results ( QuestionID, Answer, AnswerCount) as
(Select QuestionID, Answer, Count(Answer)
From SurveyResults
group by questionid, answer)
select SurveyName, SurveyQuestions, SurveyID,
(
Select question.QuestionID, results.Answer, results.AnswerCount
From question join results
on question.questionid =results.questionid
for xml auto, Root('QuestionResults'), type
)
from SurveyProfiles Join
Surveys on SurveyProfiles.SurveyProfileID = Surveys.SurveyProfileID
CREATE FUNCTION ufn_getSurveyQuestionsByID
( @.surveyId int)
returns xml
AS
BEGIN
declare @.result xml
set @.result = (
select * from v_getSurveyQuestions where SurveyID=@.surveyId
for xml Raw('Results'), Elements
)
return @.result
END
The problem is that if your tables have large amount of data, the
performance may be very poor. I recommend that you use the first method.
Hope this helps. Please feel free to let me know if you have any other
questions or concerns.
Charles Wang
Microsoft Online Community Support
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||thanks, it worked great.
"Charles Wang[MSFT]" wrote:

> Hi Chunk,
> I understand that your SQL statement including CTE failed to be executed
> with the error messages.
> If I have misunderstood, please let me know.
> The problem is that CTE statement cannot be used as a right value of SET
> expression. I tried to use a temp table to store the query result; however
> since temp table is not supported in a function, I must use a stored
> procedure to work it. Please refer to:
> CREATE PROCEDURE [dbo].[SurveyResultsXML]
> (@.SurveyID int,
> @.result xml output)
> AS
> BEGIN
> with
> question ( QuestionID ) as
> (Select distinct QuestionID From SurveyResults Where
> SurveyID=@.SurveyID),
> results ( QuestionID, Answer, AnswerCount) as
> (Select QuestionID, Answer, Count(Answer)
> From SurveyResults
> Where SurveyID=@.SurveyID
> group by questionid, answer)
> select SurveyName, SurveyQuestions,
> (
> Select question.QuestionID, results.Answer, results.AnswerCount
> From question join results
> on question.questionid =results.questionid
> for xml auto, Root('QuestionResults'), type
> ) into #TempResultTable
> from SurveyProfiles Join
> Surveys on SurveyProfiles.SurveyProfileID = Surveys.SurveyProfileI
D
> WHERE SurveyID=@.SurveyID
> set @.result = (
> select * from #TempResultTable
> for xml Raw('Results'), Elements
> )
> drop table #TempResultTable
> END
> I think that using SP is a better way; but I am not sure if you have to us
e
> a function in your situation.
> Actually I can use a view to wrap the CTE express by removing "WHERE
> SurveyID=@.SurveyID", then use a function to query from the view like this:
> CREATE VIEW v_getSurveyQuestions
> AS
> with
> question ( QuestionID ) as
> (Select distinct SurveyID, QuestionID From SurveyResults),
> results ( QuestionID, Answer, AnswerCount) as
> (Select QuestionID, Answer, Count(Answer)
> From SurveyResults
> group by questionid, answer)
> select SurveyName, SurveyQuestions, SurveyID,
> (
> Select question.QuestionID, results.Answer, results.AnswerCount
> From question join results
> on question.questionid =results.questionid
> for xml auto, Root('QuestionResults'), type
> )
> from SurveyProfiles Join
> Surveys on SurveyProfiles.SurveyProfileID = Surveys.SurveyProfileI
D
>
> CREATE FUNCTION ufn_getSurveyQuestionsByID
> ( @.surveyId int)
> returns xml
> AS
> BEGIN
> declare @.result xml
> set @.result = (
> select * from v_getSurveyQuestions where SurveyID=@.surveyId
> for xml Raw('Results'), Elements
> )
> return @.result
> END
> The problem is that if your tables have large amount of data, the
> performance may be very poor. I recommend that you use the first method.
> Hope this helps. Please feel free to let me know if you have any other
> questions or concerns.
> Charles Wang
> Microsoft Online Community Support
> ========================================
==============
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ========================================
==============
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> ========================================
==============
>
>|||Hi,
Appreciate your update and response. I am glad to hear that the suggestions
are helpful. If you have any other questions or concerns, please do not
hesitate to contact us.
Have a nice day!
Charles Wang
Microsoft Online Community Support

returning xml from function

I have a sproc I am trying to convert to a function.
The sproc works fine the sproc is giving me errors.
I am not sure if the With or the XML datatype is the problem
Msg 156, Level 15, State 1, Procedure SurveyResultsXML, Line 13
Incorrect syntax near the keyword 'with'.
Msg 319, Level 15, State 1, Procedure SurveyResultsXML, Line 13
Incorrect syntax near the keyword 'with'. If this statement is a common
table expression or an xmlnamespaces clause, the previous statement must be
terminated with a semicolon.
Msg 102, Level 15, State 1, Procedure SurveyResultsXML, Line 16
Incorrect syntax near ','.
Msg 102, Level 15, State 1, Procedure SurveyResultsXML, Line 39
Incorrect syntax near ')'.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
alter FUNCTION [dbo].[SurveyResultsXML]
(@.SurveyID int)
RETURNS XML
AS
BEGIN
declare @.result XML;
set @.result=(
with
question ( QuestionID ) as
(Select distinct QuestionID From SurveyResults Where
SurveyID=@.SurveyID),
results ( QuestionID, Answer, AnswerCount) as
(Select QuestionID, Answer, Count(Answer)
From SurveyResults
Where SurveyID=@.SurveyID
group by questionid, answer)
select SurveyName, SurveyQuestions,
(
Select question.QuestionID, results.Answer, results.AnswerCount
From question join results
on question.questionid =results.questionid
for xml auto, Root('QuestionResults'), type
)
from SurveyProfiles Join
Surveys on SurveyProfiles.SurveyProfileID = Surveys.SurveyProfileID
WHERE SurveyID=@.SurveyID
for xml Raw('Results'), Elements
);
return @.result ;
ENDHi Chunk,
I understand that your SQL statement including CTE failed to be executed
with the error messages.
If I have misunderstood, please let me know.
The problem is that CTE statement cannot be used as a right value of SET
expression. I tried to use a temp table to store the query result; however
since temp table is not supported in a function, I must use a stored
procedure to work it. Please refer to:
CREATE PROCEDURE [dbo].[SurveyResultsXML]
(@.SurveyID int,
@.result xml output)
AS
BEGIN
with
question ( QuestionID ) as
(Select distinct QuestionID From SurveyResults Where
SurveyID=@.SurveyID),
results ( QuestionID, Answer, AnswerCount) as
(Select QuestionID, Answer, Count(Answer)
From SurveyResults
Where SurveyID=@.SurveyID
group by questionid, answer)
select SurveyName, SurveyQuestions,
(
Select question.QuestionID, results.Answer, results.AnswerCount
From question join results
on question.questionid =results.questionid
for xml auto, Root('QuestionResults'), type
) into #TempResultTable
from SurveyProfiles Join
Surveys on SurveyProfiles.SurveyProfileID = Surveys.SurveyProfileID
WHERE SurveyID=@.SurveyID
set @.result = (
select * from #TempResultTable
for xml Raw('Results'), Elements
)
drop table #TempResultTable
END
I think that using SP is a better way; but I am not sure if you have to use
a function in your situation.
Actually I can use a view to wrap the CTE express by removing "WHERE
SurveyID=@.SurveyID", then use a function to query from the view like this:
CREATE VIEW v_getSurveyQuestions
AS
with
question ( QuestionID ) as
(Select distinct SurveyID, QuestionID From SurveyResults),
results ( QuestionID, Answer, AnswerCount) as
(Select QuestionID, Answer, Count(Answer)
From SurveyResults
group by questionid, answer)
select SurveyName, SurveyQuestions, SurveyID,
(
Select question.QuestionID, results.Answer, results.AnswerCount
From question join results
on question.questionid =results.questionid
for xml auto, Root('QuestionResults'), type
)
from SurveyProfiles Join
Surveys on SurveyProfiles.SurveyProfileID = Surveys.SurveyProfileID
CREATE FUNCTION ufn_getSurveyQuestionsByID
( @.surveyId int)
returns xml
AS
BEGIN
declare @.result xml
set @.result = (
select * from v_getSurveyQuestions where SurveyID=@.surveyId
for xml Raw('Results'), Elements
)
return @.result
END
The problem is that if your tables have large amount of data, the
performance may be very poor. I recommend that you use the first method.
Hope this helps. Please feel free to let me know if you have any other
questions or concerns.
Charles Wang
Microsoft Online Community Support
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||thanks, it worked great.
"Charles Wang[MSFT]" wrote:
> Hi Chunk,
> I understand that your SQL statement including CTE failed to be executed
> with the error messages.
> If I have misunderstood, please let me know.
> The problem is that CTE statement cannot be used as a right value of SET
> expression. I tried to use a temp table to store the query result; however
> since temp table is not supported in a function, I must use a stored
> procedure to work it. Please refer to:
> CREATE PROCEDURE [dbo].[SurveyResultsXML]
> (@.SurveyID int,
> @.result xml output)
> AS
> BEGIN
> with
> question ( QuestionID ) as
> (Select distinct QuestionID From SurveyResults Where
> SurveyID=@.SurveyID),
> results ( QuestionID, Answer, AnswerCount) as
> (Select QuestionID, Answer, Count(Answer)
> From SurveyResults
> Where SurveyID=@.SurveyID
> group by questionid, answer)
> select SurveyName, SurveyQuestions,
> (
> Select question.QuestionID, results.Answer, results.AnswerCount
> From question join results
> on question.questionid =results.questionid
> for xml auto, Root('QuestionResults'), type
> ) into #TempResultTable
> from SurveyProfiles Join
> Surveys on SurveyProfiles.SurveyProfileID = Surveys.SurveyProfileID
> WHERE SurveyID=@.SurveyID
> set @.result = (
> select * from #TempResultTable
> for xml Raw('Results'), Elements
> )
> drop table #TempResultTable
> END
> I think that using SP is a better way; but I am not sure if you have to use
> a function in your situation.
> Actually I can use a view to wrap the CTE express by removing "WHERE
> SurveyID=@.SurveyID", then use a function to query from the view like this:
> CREATE VIEW v_getSurveyQuestions
> AS
> with
> question ( QuestionID ) as
> (Select distinct SurveyID, QuestionID From SurveyResults),
> results ( QuestionID, Answer, AnswerCount) as
> (Select QuestionID, Answer, Count(Answer)
> From SurveyResults
> group by questionid, answer)
> select SurveyName, SurveyQuestions, SurveyID,
> (
> Select question.QuestionID, results.Answer, results.AnswerCount
> From question join results
> on question.questionid =results.questionid
> for xml auto, Root('QuestionResults'), type
> )
> from SurveyProfiles Join
> Surveys on SurveyProfiles.SurveyProfileID = Surveys.SurveyProfileID
>
> CREATE FUNCTION ufn_getSurveyQuestionsByID
> ( @.surveyId int)
> returns xml
> AS
> BEGIN
> declare @.result xml
> set @.result = (
> select * from v_getSurveyQuestions where SurveyID=@.surveyId
> for xml Raw('Results'), Elements
> )
> return @.result
> END
> The problem is that if your tables have large amount of data, the
> performance may be very poor. I recommend that you use the first method.
> Hope this helps. Please feel free to let me know if you have any other
> questions or concerns.
> Charles Wang
> Microsoft Online Community Support
> ======================================================> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ======================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
> ======================================================>
>|||Hi,
Appreciate your update and response. I am glad to hear that the suggestions
are helpful. If you have any other questions or concerns, please do not
hesitate to contact us.
Have a nice day!
Charles Wang
Microsoft Online Community Support

Returning word forms?

Microsoft SQL Server 2000 - 8.00.818 (Intel X86)
May 31 2003 16:08:15
Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
I have a list of movie titles. If I do a search on "drive" I get all the
titles that have that word (i.e. "Drive My Car") in them, but I miss titles
like "She Drives Me Crazy"
Is there some switch I'm missing somewhere, or does the MS Fulltxt engine
not do stemming? (The T-SQL help file mentions it on one page, but doesn't
offer much detail...)
I'm using the CONTAINS keyword to do the search. Should I be using
something else?
declare @.s varchar(8000)
set @.s = 'SELECT dbo.main.FILM, dbo.main.FILMYEAR, dbo.aka_film.TITLE2 as
akatitle FROM dbo.main LEFT OUTER JOIN dbo.aka_film ON dbo.main.FILMID =
dbo.aka_film.FILMID
WHERE contains(main.film, ''' + @.film + ''')
GROUP BY dbo.main.FILM, dbo.main.FILMYEAR, dbo.aka_film.TITLE2 ORDER BY
dbo.main.FILM'
exec (@.s)
[Above SQL statement broken into lines for easier reading]
Also, has anyone (third-party?) implemented a "Did you mean..." type of
thing like Google has where it suggests potential mispellings or related
searches?
Many thanks,
Jeff
"J. Knapp" <jknapp@.nospam.nospam> wrote in
news:Xns95F66B22E1C1Fjknappnospamnospam@.207.46.248 .16:

> I'm using the CONTAINS keyword to do the search. Should I be using
> something else?
Can I answer my own question and say "freetext" seems to do the trick on
this one...
I'm still curious about a "Did you mean..." engine...
|||You need to either use best bests - see Sharepoint's implementation of this
and roll your own, or do a spell check - like
http://www.spellcheck.net/
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Now available on Amazon.com
http://www.amazon.com/gp/product/off...?condition=all
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"J. Knapp" <jknapp@.nospam.nospam> wrote in message
news:Xns95F66C884D76Ejknappnospamnospam@.207.46.248 .16...
> "J. Knapp" <jknapp@.nospam.nospam> wrote in
> news:Xns95F66B22E1C1Fjknappnospamnospam@.207.46.248 .16:
>
> Can I answer my own question and say "freetext" seems to do the trick on
> this one...
> I'm still curious about a "Did you mean..." engine...

Returning Values with 2 different SELECT statements?

Hi there,

just want to find out if it's possible to extract 2 different values with 1 select statement?

I need to extract values from a table, according to a specific condition...

eg: to extract values from tableA where "office_name" = "o_id" from tableB, where tableA is the one with the primary key... and then select the office from tableA where the "officeid"=0, and populate that in the same result set...

so that it can be populated in a <asp:DropDownList> control, and then be bound to the control based on the DataValueField, and DataTextField

is it possible, or not?

thx

SJB

Hi,

Do you want more than one row, or simply only 1 ?

If you are ok with multiple rows you can just do a UNION between the single queries.

SELECT SomeName
FROM SomeTable
INNER JOIN <YourJoin>
UNION
SELECT SomeName
FROM SomeOtherTable
WHERE <condition>

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||thanks, I'll have look into it, I haven't tried in, but I will have a look, and see what happens... thx

returning values in stored proceedures

Helloooooooooooooo,

I've createde a stored proceedure that executes an INSERT.

There is a field [date] and i want to put the current date in there.

This doesn't work but there must be some easy way:
thanks in advance!!!!

-----
CREATE PROCEDURE dbo.sp_InsertSomething
@.something varchar(50),
@.date smalldatetime
AS
-- this is where i get lost
--SELECT getDate() AS mydate
--@.date = mydate

INSERT INTO [dbo].[mytable] (something, [date])
VALUES(
@.something,
@.date
)

GO
------Why not set the default of the field to getDate() in the table ?|||Originally posted by rnealejr
Why not set the default of the field to getDate() in the table ?

oh man that is a really good idea. how dumb am i!|||alternatively...

INSERT INTO [dbo].[mytable] (something, [date])
VALUES(@.something, getDate())

Returning Values in a stored procedure

Does anyone out there know how to create a stored procedure that will return results based on the information queried?

For example:

I have a table myTable With the following fields:
anIdentifier, anAction, data1, data2, data3, data4

I want to be able to do something like:

select * from myTable where anIdentifier = 123
if anAction = 1 return anIdentifier, anAction, data1, data3
if anAction = 2 return anIdentifier, anAction, data2, data3
if anAction = 3 return anIdentifier, anAction, data1, data2, data3

Can a stored procedure be created that can do this or am I just dreaming?Originally posted by rwaver
Does anyone out there know how to create a stored procedure that will return results based on the information queried?

For example:

I have a table myTable With the following fields:
anIdentifier, anAction, data1, data2, data3, data4

I want to be able to do something like:

select * from myTable where anIdentifier = 123
if anAction = 1 return anIdentifier, anAction, data1, data3
if anAction = 2 return anIdentifier, anAction, data2, data3
if anAction = 3 return anIdentifier, anAction, data1, data2, data3

Can a stored procedure be created that can do this or am I just dreaming?

you can try to use case ... when ... then ...

Returning values from unique index

I have a table1(key, field1, field2, field3...).
My primary key is "key".
Then i create a unique index for fields "field1" and "field2".
This works, but i want that sql server retrieve me the values thar are being
duplicated.
I'm using sql server 7.0.
Ex: Table1 as the following values:
-0;0;0;0;
-1;1;1;1;
-2;2;2;2;
I have a second table with the values:
-3;3;3;3;
-4;1;1;1;
-5;0;0;0;
-6;0;0;0;
if i do a insert (insert into table1 select * from table2), it gives me an
error but i want that sql tell me that the error was because of values
4;1;1;1-5;0;0;0-6;0;0;0.
I hope this helps.
Can anyone help me?What was the error you got?
Madhivanan|||SQL Server won't identify the duplicated rows automatically for you.
You'll have to do a separate query:
SELECT T2.col1, T2.col2, T2.col3, T2.col4
FROM Table2 AS T2
JOIN Table1 AS T1
ON T1.col1 = T2.col1
AND T1.col2 = T2.col2
You could also insert only the unique rows as follows:
INSERT INTO Table1 (col1, col2, cole, col4)
SELECT T2.col1, T2.col2, T2.col3, T2.col4
FROM Table2 AS T2
LEFT JOIN Table1 AS T1
ON T1.col1 = T2.col1
AND T1.col2 = T2.col2
WHERE T1.col1 IS NULL
David Portas
SQL Server MVP
--|||Hi David,

INSERT INTO Table1 (col1, col2, cole, col4)
SELECT T2.col1, T2.col2, T2.col3, T2.col4
FROM Table2 AS T2
LEFT JOIN Table1 AS T1
ON T1.col1 = T2.col1
AND T1.col2 = T2.col2
WHERE T1.col1 IS NULL <<
I think that a DISTINCT should be added to the SELECT list.
Cheers,
--
BG, SQL Server MVP
www.SolidQualityLearning.com
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1111143246.610107.236070@.g14g2000cwa.googlegroups.com...
> SQL Server won't identify the duplicated rows automatically for you.
> You'll have to do a separate query:
> SELECT T2.col1, T2.col2, T2.col3, T2.col4
> FROM Table2 AS T2
> JOIN Table1 AS T1
> ON T1.col1 = T2.col1
> AND T1.col2 = T2.col2
> You could also insert only the unique rows as follows:
> INSERT INTO Table1 (col1, col2, cole, col4)
> SELECT T2.col1, T2.col2, T2.col3, T2.col4
> FROM Table2 AS T2
> LEFT JOIN Table1 AS T1
> ON T1.col1 = T2.col1
> AND T1.col2 = T2.col2
> WHERE T1.col1 IS NULL
> --
> David Portas
> SQL Server MVP
> --
>|||On Fri, 18 Mar 2005 10:26:40 -0000, Joaquim Meireles wrote:

>I have a table1(key, field1, field2, field3...).
>My primary key is "key".
>Then i create a unique index for fields "field1" and "field2".
>This works, but i want that sql server retrieve me the values thar are bein
g
>duplicated.
>I'm using sql server 7.0.
>Ex: Table1 as the following values:
> -0;0;0;0;
> -1;1;1;1;
> -2;2;2;2;
>I have a second table with the values:
> -3;3;3;3;
> -4;1;1;1;
> -5;0;0;0;
> -6;0;0;0;
>if i do a insert (insert into table1 select * from table2), it gives me an
>error but i want that sql tell me that the error was because of values
>4;1;1;1-5;0;0;0-6;0;0;0.
Hi Joaquim,
A constraint won't do that - a constraint simply constrains, that's all.
As David says, you need to use a query. However, the query he posted
won't return all duplicate rows. Try this one instead:
SELECT T2.key, T2.col1, T2.col2, T2.col3, T2.col4
FROM Table2 AS T2
LEFT JOIN Table1 AS T1
ON T1.col1 = T2.col1
AND T1.col2 = T2.col2
WHERE T1.key IS NOT NULL
OR EXISTS (SELECT *
FROM Table2 AS T2b
WHERE T2b.col1 = T2.col1
AND T2b.col2 = T2.col2
AND T2b.key <> T2.key)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Returning values from Stored Procedure

Hi Using Following Stored Procedure,

Which always returns Null,

What s the error,

CREATE PROCEDURE prLoginAuth

(

@.pStrUserName varchar(50),
@.pStrPassword varchar(50),
@.pOutput Varchar(20) Output
)

AS

Declare @.V_Facilities Varchar(50)

SELECT Facilities=@.V_Facilities From UserLoginFacilities where LoginID=(Select LoginID From UserLogin where LoginName=@.pStrUserName and Password=@.pStrPassword)

If(@.V_Facilities=null)

Set @.pOutput = @.V_Facilities

Return @.pOutput;

Else

Set @.pOutput = @.V_Facilities

Return @.pOutput;

GO

Anyone correct this query , I want return the output from this procedure

Thanx in advance

Selva.R

I believe you are mixing two techniques for returning data from a stored procedure.

1. If you declare the parameter 'pOutput' as an output type parameter, you should NOT use it as the return value from the stored procedure. Simply assigning a value to it does the trick. (e.g. Select @.pOutput = @.V_Facilities). You pickup the result from your parameters collection.

2. If you decide to return the result as the returncode from the stored procedure, there is no need to define a parameter from the type OUTPUT. You just return the value (e.g. RETURN @.V_Facilities) and pick it up as the return value from your ExecuteScalar method

Hope this helps.

|||

In addition to what has already been mentioned, you can only ever return an integer using RETURN in a stored procedure. So any value that you try to return would have to be convertable to an integer ('0001' ok, 'hey joe' not ok :)

Second, you are never setting the value of @.V_Facilites, and I am guessing (since you didn't provide data or output :) that you are getting back a result set with a null value, as well as the return value = null

To set the value of a parameter to a column value, the syntax is:

SELECT @.variableName = column
FROM ....

Turned the other way around, it is an older syntax for renaming the output name of a column, so it wouldn't matter what was in the FROM clause, it would still return the value of the variable (in this case null since you haven't initialized it). For example:

DECLARE @.testValue int
SET @.testValue = 1

SELECT bob = @.testValue
--FROM anything

Returns

bob
--
1

Returning values from Stored Procedure

Hi,
How to return values from stored procedures?? I have a value whose variable would be set thru this sp and it should return this value. How to do this?

Thanks,It depends on whether you want a *return* value or an *output* value. You can return only one value, but many output params.

Example of return value:

CREATE PROCEDURE myProc1
@.Param INT
AS
DECLARE @.Return INT
SELECT @.Return=SomeColumn FROM MyTable
 WHERE SomeKey=@.Param
RETURN @.Return
GO

Example of output parameters:

CREATE PROCEDURE myProc2
@.Param INT,
@.Output1 NVARCHAR(20) OUTPUT,
@.Output2 NVARCHAR(50) OUTPUT
AS
SELECT @.Outpu1=SomeColumn, @.Output2=AnotherColumn FROM MyTable
 WHERE SomeKey=@.Param
GO

HTH!

returning values from sp_executesql statements

I'm trying to return a set the value of a variable with the output from a sp_executesql statement, but I'm not sure how to do it. Basically, what I want to do is:

set @.sql = 'select time from ' + @.tablename + ' where id = ' + @.int

How do I go about doing this?I don't understand why do you need sp_executesql for that
You can use a simple SQL statement like:

select @.return_value = time from your_table where id = @.int

or

select @.return_value = (select time from your_table where id = @.int)

Originally posted by dez182
I'm trying to return a set the value of a variable with the output from a sp_executesql statement, but I'm not sure how to do it. Basically, what I want to do is:

set @.sql = 'select time from ' + @.tablename + ' where id = ' + @.int

How do I go about doing this?|||because the table name that I'm selecting from need to change depending on user input.|||Hi try this.

DECLARE @.ssql varchar(255)
DECLARE @.tablename varchar(40)
DECLARE @.int int

SELECT @.tablename = 'sometable'
SELECT @.int = 5
SELECT @.ssql = 'select time from ' + @.tablename
SELECT @.ssql = @.ssql + ' where id = ' + CONVERT(VARCHAR(16),@.int)

EXEC(@.ssql)

Returning values from a stored procedure - Help needed!

Hello everyone,
I'm using the following procedure to return the result of a zip code
radius search at my website. As it is, the proc is working fine except
I'm having a problem returning a value for the distance between 2
points. The procedure calculates the distance between the 2 points and
compares it in the following select statement
SELECT @.Result as [Result], bsID, bsFirstName, bsAge, bsIntsection,
bsStartWage, bsStatus FROM tbSitters WHERE bsZipCode in (SELECT ZipCode
FROM tbZip WHERE @.distance > 3959 * ACOS(SIN(@.lat/57.3) *
SIN(Latitude/57.3) + COS(@.lat/57.3) * COS(Latitude/57.3) *
COS((Longitude/57.3) - (@.lng/57.3))) )
end;
BROKEN DOWN
****@.distance is the distance inputed by a user (form post - value up
to 20 miles) and this formula calculates the distance between 2
points*****
>3959 * ACOS(SIN(@.lat/57.3) * SIN(Latitude/57.3) + COS(@.lat/57.3) * COS(Latitude/57.
3) * COS((Longitude/57.3) - (@.lng/57.3)))
****Now what I need to know is how can i return this distance value
through the procedure.
(3959 * ACOS(SIN(@.lat/57.3) * SIN(Latitude/57.3) + COS(@.lat/57.3) *
COS(Latitude/57.3) * COS((Longitude/57.3) - (@.lng/57.3))))
---
Following is the complete proc
---
CREATE PROCEDURE dbo.psFindSitters1
(@.zipcode char(7),
@.distance int
)
AS
SET NOCOUNT ON
Declare @.Result varchar(90)
Declare @.lat decimal(9,6)
Declare @.lng decimal(9,6)
Declare @.lat1 decimal(9,6)
Declare @.lng1 decimal(9,6)
Declare @.Latitude decimal(9,6)
Declare @.Longitude decimal(9,6)
Declare @.count int
Declare @.distance1 int
Declare @.distance2 int
set @.Result = 'err';
--find the parent
if (@.zipcode<>0) begin --find LAT and LNG
select @.lat=[Latitude],@.lng=[Longitude] FROM tbZip WHERE ZipCode =
@.zipcode
end;
if (@.lat<> 0) begin --another query
SELECT @.count=count(*) FROM tbSitters WHERE bsZipCode in (SELECT
ZipCode FROM tbZip WHERE @.distance > 3959 * ACOS(SIN(@.lat/57.3) *
SIN(Latitude/57.3) + COS(@.lat/57.3) * COS(Latitude/57.3) *
COS((Longitude/57.3) - (@.lng/57.3))) )
end;
if (@.count > 0) begin --another query
set @.Result = 'user';
SELECT @.Result as [Result], bsID, bsFirstName, bsAge, bsIntsection,
bsStartWage, bsStatus FROM tbSitters WHERE bsZipCode in (SELECT ZipCode
FROM tbZip WHERE @.distance > 3959 * ACOS(SIN(@.lat/57.3) *
SIN(Latitude/57.3) + COS(@.lat/57.3) * COS(Latitude/57.3) *
COS((Longitude/57.3) - (@.lng/57.3))) )
end;
if (@.lat is null or @.lat=0 or @.count is null or @.count=0) begin
set @.Result = 'nouser';
select @.Result as [Result];
--GoTo hasErr;
end;
GO
Thanks in advance for any help!
RobertIf you want the distance to sitter returned in the (table) result set, join
tbSitters to tbZip
(*untested*)
if (@.count > 0) begin --another query
set @.Result = 'user';
SELECT @.Result as [Result], s.bsID, s.bsFirstName, s.bsAge, s.bsIntsection,
s.bsStartWage, s.bsStatus,
(3959 * ACOS(SIN(@.lat/57.3) * SIN(z.Latitude/57.3) + COS(@.lat/57.3) *
COS(z.Latitude/57.3) * COS((z.Longitude/57.3) - (@.lng/57.3)))) AS "Distance"
FROM tbSitters s INNER JOIN tbZip z ON s.bsZipCode = z.ZipCode
WHERE s.bsZipCode in (SELECT ZipCode
FROM tbZip WHERE @.distance > 3959 * ACOS(SIN(@.lat/57.3) *
SIN(Latitude/57.3) + COS(@.lat/57.3) * COS(Latitude/57.3) *
COS((Longitude/57.3) - (@.lng/57.3))) )
end;
"ROBinBRAMPTON" wrote:

> Hello everyone,
> I'm using the following procedure to return the result of a zip code
> radius search at my website. As it is, the proc is working fine except
> I'm having a problem returning a value for the distance between 2
> points. The procedure calculates the distance between the 2 points and
> compares it in the following select statement
> SELECT @.Result as [Result], bsID, bsFirstName, bsAge, bsIntsection,
> bsStartWage, bsStatus FROM tbSitters WHERE bsZipCode in (SELECT ZipCode
> FROM tbZip WHERE @.distance > 3959 * ACOS(SIN(@.lat/57.3) *
> SIN(Latitude/57.3) + COS(@.lat/57.3) * COS(Latitude/57.3) *
> COS((Longitude/57.3) - (@.lng/57.3))) )
> end;
> BROKEN DOWN
> ****@.distance is the distance inputed by a user (form post - value up
> to 20 miles) and this formula calculates the distance between 2
> points*****
> ****Now what I need to know is how can i return this distance value
> through the procedure.
> (3959 * ACOS(SIN(@.lat/57.3) * SIN(Latitude/57.3) + COS(@.lat/57.3) *
> COS(Latitude/57.3) * COS((Longitude/57.3) - (@.lng/57.3))))
> ---
> Following is the complete proc
> ---
> CREATE PROCEDURE dbo.psFindSitters1
> (@.zipcode char(7),
> @.distance int
> )
> AS
> SET NOCOUNT ON
> Declare @.Result varchar(90)
> Declare @.lat decimal(9,6)
> Declare @.lng decimal(9,6)
> Declare @.lat1 decimal(9,6)
> Declare @.lng1 decimal(9,6)
> Declare @.Latitude decimal(9,6)
> Declare @.Longitude decimal(9,6)
> Declare @.count int
> Declare @.distance1 int
> Declare @.distance2 int
> set @.Result = 'err';
> --find the parent
> if (@.zipcode<>0) begin --find LAT and LNG
> select @.lat=[Latitude],@.lng=[Longitude] FROM tbZip WHERE ZipCode =
> @.zipcode
> end;
> if (@.lat<> 0) begin --another query
> SELECT @.count=count(*) FROM tbSitters WHERE bsZipCode in (SELECT
> ZipCode FROM tbZip WHERE @.distance > 3959 * ACOS(SIN(@.lat/57.3) *
> SIN(Latitude/57.3) + COS(@.lat/57.3) * COS(Latitude/57.3) *
> COS((Longitude/57.3) - (@.lng/57.3))) )
> end;
>
> if (@.count > 0) begin --another query
> set @.Result = 'user';
> SELECT @.Result as [Result], bsID, bsFirstName, bsAge, bsIntsection,
> bsStartWage, bsStatus FROM tbSitters WHERE bsZipCode in (SELECT ZipCode
> FROM tbZip WHERE @.distance > 3959 * ACOS(SIN(@.lat/57.3) *
> SIN(Latitude/57.3) + COS(@.lat/57.3) * COS(Latitude/57.3) *
> COS((Longitude/57.3) - (@.lng/57.3))) )
> end;
> if (@.lat is null or @.lat=0 or @.count is null or @.count=0) begin
> set @.Result = 'nouser';
> select @.Result as [Result];
> --GoTo hasErr;
> end;
>
> GO
> Thanks in advance for any help!
> Robert
>|||It worked perfectly!
Thanks very much Mark
Robert
p.s. If you're a parent in need of a sitter? Open an account then drop
me an email at admin at phone a babysitter dot com, and I'll upgrade it
for you as way way to express my gratitude.|||Also consider a re-write of the procedure using a temporary table
CREATE PROCEDURE dbo.psFindSitters1
(@.zipcode char(7),
@.distance int
)
AS
SET NOCOUNT ON
Declare @.Result varchar(90)
Declare @.lat decimal(9,6)
Declare @.lng decimal(9,6)
Declare @.lat1 decimal(9,6)
Declare @.lng1 decimal(9,6)
Declare @.Latitude decimal(9,6)
Declare @.Longitude decimal(9,6)
Declare @.count int
Declare @.distance1 int
Declare @.distance2 int
set @.Result = 'err';
--find the parent
if (@.zipcode<>0) begin --find LAT and LNG
select @.lat=[Latitude],@.lng=[Longitude] FROM tbZip WHERE ZipCode =
@.zipcode
end;
if (@.lat<> 0) begin --another query
SELECT ZipCode INTO #zipsinrange
FROM tbZip WHERE @.distance > 3959 * ACOS(SIN(@.lat/57.3) *
SIN(Latitude/57.3) + COS(@.lat/57.3) * COS(Latitude/57.3) *
COS((Longitude/57.3) - (@.lng/57.3))) )
set @.count = @.@.ROWCOUNT
end;
if (@.count > 0) begin --another query
set @.Result = 'user';
SELECT @.Result as [Result], s.bsID, s.bsFirstName, s.bsAge, s.bsIntsection,
s.bsStartWage, s.bsStatus,
(3959 * ACOS(SIN(@.lat/57.3) * SIN(z.Latitude/57.3) + COS(@.lat/57.3) *
COS(z.Latitude/57.3) * COS((z.Longitude/57.3) - (@.lng/57.3)))) AS "Distance"
FROM tbSitters s INNER JOIN #zipsinrange r ON s.bsZipCode = r.ZipCode
INNER JOIN tblZip z ON z.ZipCode = r.ZipCode
end;
if (@.lat is null or @.lat=0 or @.count is null or @.count=0) begin
set @.Result = 'nouser';
select @.Result as [Result];
--GoTo hasErr;
end;
GO
"ROBinBRAMPTON" wrote:

> Hello everyone,
> I'm using the following procedure to return the result of a zip code
> radius search at my website. As it is, the proc is working fine except
> I'm having a problem returning a value for the distance between 2
> points. The procedure calculates the distance between the 2 points and
> compares it in the following select statement
> SELECT @.Result as [Result], bsID, bsFirstName, bsAge, bsIntsection,
> bsStartWage, bsStatus FROM tbSitters WHERE bsZipCode in (SELECT ZipCode
> FROM tbZip WHERE @.distance > 3959 * ACOS(SIN(@.lat/57.3) *
> SIN(Latitude/57.3) + COS(@.lat/57.3) * COS(Latitude/57.3) *
> COS((Longitude/57.3) - (@.lng/57.3))) )
> end;
> BROKEN DOWN
> ****@.distance is the distance inputed by a user (form post - value up
> to 20 miles) and this formula calculates the distance between 2
> points*****
> ****Now what I need to know is how can i return this distance value
> through the procedure.
> (3959 * ACOS(SIN(@.lat/57.3) * SIN(Latitude/57.3) + COS(@.lat/57.3) *
> COS(Latitude/57.3) * COS((Longitude/57.3) - (@.lng/57.3))))
> ---
> Following is the complete proc
> ---
> CREATE PROCEDURE dbo.psFindSitters1
> (@.zipcode char(7),
> @.distance int
> )
> AS
> SET NOCOUNT ON
> Declare @.Result varchar(90)
> Declare @.lat decimal(9,6)
> Declare @.lng decimal(9,6)
> Declare @.lat1 decimal(9,6)
> Declare @.lng1 decimal(9,6)
> Declare @.Latitude decimal(9,6)
> Declare @.Longitude decimal(9,6)
> Declare @.count int
> Declare @.distance1 int
> Declare @.distance2 int
> set @.Result = 'err';
> --find the parent
> if (@.zipcode<>0) begin --find LAT and LNG
> select @.lat=[Latitude],@.lng=[Longitude] FROM tbZip WHERE ZipCode =
> @.zipcode
> end;
> if (@.lat<> 0) begin --another query
> SELECT @.count=count(*) FROM tbSitters WHERE bsZipCode in (SELECT
> ZipCode FROM tbZip WHERE @.distance > 3959 * ACOS(SIN(@.lat/57.3) *
> SIN(Latitude/57.3) + COS(@.lat/57.3) * COS(Latitude/57.3) *
> COS((Longitude/57.3) - (@.lng/57.3))) )
> end;
>
> if (@.count > 0) begin --another query
> set @.Result = 'user';
> SELECT @.Result as [Result], bsID, bsFirstName, bsAge, bsIntsection,
> bsStartWage, bsStatus FROM tbSitters WHERE bsZipCode in (SELECT ZipCode
> FROM tbZip WHERE @.distance > 3959 * ACOS(SIN(@.lat/57.3) *
> SIN(Latitude/57.3) + COS(@.lat/57.3) * COS(Latitude/57.3) *
> COS((Longitude/57.3) - (@.lng/57.3))) )
> end;
> if (@.lat is null or @.lat=0 or @.count is null or @.count=0) begin
> set @.Result = 'nouser';
> select @.Result as [Result];
> --GoTo hasErr;
> end;
>
> GO
> Thanks in advance for any help!
> Robert
>

returning values from a stored procedure

i have a vb6 app which accesses a sql db. i'm using stored procedures to retun valus and recordsets. my question is can i return both a full recordset from one table and a single value from another table with in one stored procedure?.

example:
i have tblcustomers and tblitems, i want to return a full recordset from tblcustomers for a specific customer and i want to retun the item price for a selected item.

can retrive both values in one stored procedure or do i need to create on stored procedure for each.

Thank You,
ThomasOriginally posted by thomas eicondev
i have a vb6 app which accesses a sql db. i'm using stored procedures to retun valus and recordsets. my question is can i return both a full recordset from one table and a single value from another table with in one stored procedure?.

example:
i have tblcustomers and tblitems, i want to return a full recordset from tblcustomers for a specific customer and i want to retun the item price for a selected item.

can retrive both values in one stored procedure or do i need to create on stored procedure for each.

Thank You,
Thomas

If it is OK with you to return two record sets one for tblcustomers table and one for the price selected from tblitems, yes is possible.
In this case the return set for the price will be a table with one field and one record...|||Originally posted by ioana
If it is OK with you to return two record sets one for tblcustomers table and one for the price selected from tblitems, yes is possible.
In this case the return set for the price will be a table with one field and one record...

Also, you can use UNION in stored procedure - just add special field where you can set from what table row is.

Returning Values from a Dynamic SQL

I have an accounting database which contains data from various years.
The frontend is a VB.Net program. At the year end, the program creates
new voucher and transaction tables and creates new stored procedures for
them.

I just append the 'new year' at the end and create them

ie, Vouchers2001, Vouchers2002, Vouchers2003
Similarly Transactions2001, Transactions2002.

The data for all the years is in the same database.

Also, I maintain a table called 'Books' which contains the Years for
which data is present in the Database. The Structure of the Books table
is

BookID BookYear
1 2001
2 2002
3 2003
4 2004

My Problem is that i need to know the current balance of any ledger for
any year. The method to calculate the balance for any year is to start
from the Minimum year in the Books table and continue upto the required
year. The SQL is as follows.

DECLARE @.iLedgerID AS INT --will be passed as parameter
DECLARE @.iYear as INT --will be passed as parameter
DECLARE @.CurrentBalance as MONEY

SET @.iLedgerID =1

DECLARE @.MinBook as INTEGER
DECLARE @.String nVarchar(4000)

SELECT @.MinBook = Min(BookYear)
FROM Books

WHILE @.MinBook <= @.iYear
BEGIN

SET @.String = ' DECLARE @.TT Money ' + char(13) +
' SELECT @.TT = ISNULL( SUM( ISNULL(Debit,0) - ISNULL(Credit,0) ),0 )'
+ ' FROM transactions' + CAST(@.MinBook AS CHAR(4)) + ' LEFT OUTER JOIN
dbo.Vouchers' + CAST(@.MinBook AS CHAR(4)) + ' ON dbo.Transactions' +
CAST(@.MinBook AS CHAR(4)) + '.VoucherID' + ' = dbo.Vouchers' +
CAST(@.MinBook AS CHAR(4)) + '.VoucherID ' +
'WHERE (LedgerID = @.iLedgerID)'

EXEC sp_executesql @.String, N'@.iLedgerID Int', @.iLedgerID */

SET @.MinBook = @.MinBook + 1
END

Now this is just a sample code. It may have a few glitches. My question
is

a) Do I have to create a dynamic sql if the name of the database is not
known ahead of time. If No then
b) I need to add the balance of each year to the grand total. How do i
return a value from a dynamic sql.

TIA

*** Sent via Developersdex http://www.developersdex.com ***Oops, I missed out the last line in the string varible. The last line
returns the calculated variable. The SQL docmentation says that Return
can only return integer types. so how do i return a money value. I
cannot insert into a temporary table because the application is
Multi-User and may fail.

WHILE @.MinBook <= @.iYear
BEGIN

SET @.String =' DECLARE @.TT Money ' + char(13) +
' SELECT @.TT = ISNULL( SUM( ISNULL(Debit,0) - ISNULL(Credit,0) ),0 )' +
' FROM transactions' + CAST(@.MinBook AS CHAR(4)) + ' LEFT OUTER JOIN
dbo.Vouchers' + CAST(@.MinBook AS CHAR(4)) + ' ON dbo.Transactions' +
CAST(@.MinBook AS CHAR(4)) + '.VoucherID' +
' = dbo.Vouchers' + CAST(@.MinBook AS CHAR(4)) + '.VoucherID ' + 'WHERE
(LedgerID = @.iLedgerID)' + char(13) +
' Return @.TT'

EXEC sp_executesql @.String, N'@.iLedgerID Int', @.iLedgerID

SET @.MinBook = @.MinBook + 1
END

*** Sent via Developersdex http://www.developersdex.com ***|||Bill Bob (nospam@.devdex.com) writes:
> Oops, I missed out the last line in the string varible. The last line
> returns the calculated variable. The SQL docmentation says that Return
> can only return integer types. so how do i return a money value. I
> cannot insert into a temporary table because the application is
> Multi-User and may fail.

1) You would have a much simpler task, if you made the year a key in the
Vouchers and Transactions table, rather than having one table per year.

2) So you don't have the possibility to do that, but then create views:

CREATE VIEW Vouchers AS
SELECT Year = '2000', * FROM Vouchers2000
UNION ALL
SELECT '2001', * FROM Vouchers2001
UNION ALL
...

3) That you cannot use temp tabls in a multi-user environment is a mis-
understanding. Temp tables are visible for the local connection only.

4) However, ther prefer method for getting scalar data back from
sp_executesql is output parameters. For a quick example, see
http://www.sommarskog.se/dynamic_sql.html#sp_executesql.

--
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|||
> a) Do I have to create a dynamic sql if the name of the database is not
> known ahead of time.

Yes

> b) I need to add the balance of each year to the grand total. How do i
> return a value from a dynamic sql.

You can use OUTPUT parameters in sp_executesql

SET @.String = ' SELECT @.TT = ISNULL( SUM( ISNULL(Debit,0) -
ISNULL(Credit,0) ),0 )' +
' FROM transactions' + CAST(@.MinBook AS CHAR(4)) + ' LEFT OUTER JOIN
dbo.Vouchers' + CAST(@.MinBook AS CHAR(4)) + ' ON dbo.Transactions' +
CAST(@.MinBook AS CHAR(4)) + '.VoucherID' +
' = dbo.Vouchers' + CAST(@.MinBook AS CHAR(4)) + '.VoucherID ' + 'WHERE
(LedgerID = @.iLedgerID)' + char(13) +
' Return @.TT'

DECLARE @.TT Money
EXEC sp_executesql @.String, N'@.iLedgerID Int, @.TT Money', @.iLedgerID ,
@.TT OUTPUT|||... should be this

EXEC sp_executesql @.String, N'@.iLedgerID Int, @.TT Money OUTPUT',
@.iLedgerID ,
@.TT OUTPUT|||> 1) You would have a much simpler task, if you made the year > a key in
the Vouchers and Transactions table, rather than > having one table per
year.

My intial problem was something else. So, I had to switch to different
table for different years.

Each Voucher Table contains a VoucherDate and a VoucherTypeID and a
VoucherNo. The VoucherNo must be unique for a vouchertype and within a
financial year (1st April - 31st March).

SQL Server cannot create a unique index with this criteria. Hence, I had
to split the tables.

*** Sent via Developersdex http://www.developersdex.com ***|||Bill Bob (nospam@.devdex.com) writes:
> Each Voucher Table contains a VoucherDate and a VoucherTypeID and a
> VoucherNo. The VoucherNo must be unique for a vouchertype and within a
> financial year (1st April - 31st March).
> SQL Server cannot create a unique index with this criteria. Hence, I had
> to split the tables.

Of course it can! You would add Year as a column in the table, and the
primary key would be (Year, VoucherTypeID, VoucherNo).

--
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

Returning Value from stored procedure

I wrote a Stored Procedure as below:
CREATE PROCEDURE sp_AssignActPass (@.CustID varchar(4), @.UserID
varchar(7),@.ActPass varchar(45),@.LastUpdate datetime)
AS
UPDATE MemberSET ActPass = @.ActPass, LastUpdate = CURRENT_TIMESTAMP
WHERE CustID = @.CustID AND UserID = @.UserID AND LastUpdate = @.LastUpdate
IF @.@.ROWCOUNT = 0
BEGIN
IF EXISTS(SELECT UserID FROM Member WHERE CustID = @.CustID AND UserID =
@.UserID)
RETURN '2' -- Concurrency Conflict
ELSE
RETURN '1' -- Record has been deleted
END
ELSE
RETURN '0' -- Record has been updated
GO
The content is not important. The issue here is when I execute this Stored
Procedure, I don't get 0, 1 or 2 as a result. It only tells me how many rows
are affected. Why? I thought I should see 0, 1, or 2?
Thank you in advancere-write the Procedure as
CREATE PROCEDURE sp_AssignActPass (@.CustID varchar(4), @.UserID
varchar(7),@.ActPass varchar(45),@.LastUpdate datetime), @.result int OUTPUT
AS
UPDATE MemberSET ActPass = @.ActPass, LastUpdate = CURRENT_TIMESTAMP
WHERE CustID = @.CustID AND UserID = @.UserID AND LastUpdate = @.LastUpdate
IF @.@.ROWCOUNT = 0
BEGIN
IF EXISTS(SELECT UserID FROM Member WHERE CustID = @.CustID AND UserID =
@.UserID)
SET @.result = 2
ELSE
SET @.result = 1
END
ELSE
SET @.result = 0
RETURN @.result
GO
best Regards,
Chandra
---
"wrytat" wrote:

> I wrote a Stored Procedure as below:
> CREATE PROCEDURE sp_AssignActPass (@.CustID varchar(4), @.UserID
> varchar(7),@.ActPass varchar(45),@.LastUpdate datetime)
> AS
> UPDATE MemberSET ActPass = @.ActPass, LastUpdate = CURRENT_TIMESTAMP
> WHERE CustID = @.CustID AND UserID = @.UserID AND LastUpdate = @.LastUpdate
> IF @.@.ROWCOUNT = 0
> BEGIN
> IF EXISTS(SELECT UserID FROM Member WHERE CustID = @.CustID AND UserID =
> @.UserID)
> RETURN '2' -- Concurrency Conflict
> ELSE
> RETURN '1' -- Record has been deleted
> END
> ELSE
> RETURN '0' -- Record has been updated
> GO
> The content is not important. The issue here is when I execute this Stored
> Procedure, I don't get 0, 1 or 2 as a result. It only tells me how many ro
ws
> are affected. Why? I thought I should see 0, 1, or 2?
> Thank you in advance|||The return value isnt present to the result, it is a result which can be
get via special properties in the providers, or within a TSQL statement:
DECLARE Returncode int
EXEC @.RC = Someproc
YOu can refer to the Return syntax on BOL.
Sample:
<BOLSample>
CREATE PROCEDURE checkstate @.param varchar(11)
AS
IF (SELECT state FROM authors WHERE au_id = @.param) = 'CA'
RETURN 1
ELSE
RETURN 2
DECLARE @.return_status int
EXEC @.return_status = checkstate '172-32-1176'
SELECT 'Return Status' = @.return_status
GO
</BOLSample>
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"wrytat" <wrytat@.discussions.microsoft.com> schrieb im Newsbeitrag
news:466A14D8-2256-47D2-8ECC-C0576672078F@.microsoft.com...
>I wrote a Stored Procedure as below:
> CREATE PROCEDURE sp_AssignActPass (@.CustID varchar(4), @.UserID
> varchar(7),@.ActPass varchar(45),@.LastUpdate datetime)
> AS
> UPDATE MemberSET ActPass = @.ActPass, LastUpdate = CURRENT_TIMESTAMP
> WHERE CustID = @.CustID AND UserID = @.UserID AND LastUpdate = @.LastUpdate
> IF @.@.ROWCOUNT = 0
> BEGIN
> IF EXISTS(SELECT UserID FROM Member WHERE CustID = @.CustID AND UserID =
> @.UserID)
> RETURN '2' -- Concurrency Conflict
> ELSE
> RETURN '1' -- Record has been deleted
> END
> ELSE
> RETURN '0' -- Record has been updated
> GO
> The content is not important. The issue here is when I execute this Stored
> Procedure, I don't get 0, 1 or 2 as a result. It only tells me how many
> rows
> are affected. Why? I thought I should see 0, 1, or 2?
> Thank you in advance|||I get an error message.
Erro1 170: Line 3: Incorrect syntax near ','.
Must declare the variable '@.result'.
Must declare the variable '@.result'.
Must declare the variable '@.result'.
Must declare the variable '@.result'.
"Chandra" wrote:
> re-write the Procedure as
> CREATE PROCEDURE sp_AssignActPass (@.CustID varchar(4), @.UserID
> varchar(7),@.ActPass varchar(45),@.LastUpdate datetime), @.result int OUTPUT
> AS
> UPDATE MemberSET ActPass = @.ActPass, LastUpdate = CURRENT_TIMESTAMP
> WHERE CustID = @.CustID AND UserID = @.UserID AND LastUpdate = @.LastUpdate
> IF @.@.ROWCOUNT = 0
> BEGIN
> IF EXISTS(SELECT UserID FROM Member WHERE CustID = @.CustID AND UserID =
> @.UserID)
> SET @.result = 2
> ELSE
> SET @.result = 1
> END
> ELSE
> SET @.result = 0
> RETURN @.result
> GO
>
> --
> best Regards,
> Chandra
> ---
>
> "wrytat" wrote:
>|||Then is it possible to get this returned value in the codings of ASP.nEt
"Jens Sü?meyer" wrote:

> The return value isn′t present to the result, it is a result which can be
> get via special properties in the providers, or within a TSQL statement:
> DECLARE Returncode int
> EXEC @.RC = Someproc
> YOu can refer to the Return syntax on BOL.
> Sample:
> <BOLSample>
> CREATE PROCEDURE checkstate @.param varchar(11)
> AS
> IF (SELECT state FROM authors WHERE au_id = @.param) = 'CA'
> RETURN 1
> ELSE
> RETURN 2
> DECLARE @.return_status int
> EXEC @.return_status = checkstate '172-32-1176'
> SELECT 'Return Status' = @.return_status
> GO
> </BOLSample>
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "wrytat" <wrytat@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:466A14D8-2256-47D2-8ECC-C0576672078F@.microsoft.com...
>
>|||Just declare it here:
WHERE CustID = @.CustID AND UserID = @.UserID AND LastUpdate = @.LastUpdate
DECLARE @.Result INT --<-- HERE
IF @.@.ROWCOUNT = 0
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"wrytat" <wrytat@.discussions.microsoft.com> schrieb im Newsbeitrag
news:B7C2766B-C908-4350-ADEC-BBCA688C88B1@.microsoft.com...
>I get an error message.
> Erro1 170: Line 3: Incorrect syntax near ','.
> Must declare the variable '@.result'.
> Must declare the variable '@.result'.
> Must declare the variable '@.result'.
> Must declare the variable '@.result'.
>
> "Chandra" wrote:
>|||Just not to let you run in further problems, it depends on how you get dour
data:
http://support.microsoft.com/kb/308051/EN-US/
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"wrytat" <wrytat@.discussions.microsoft.com> schrieb im Newsbeitrag
news:FDE41EAF-8646-404A-A22E-0602471360FF@.microsoft.com...
> Then is it possible to get this returned value in the codings of ASP.nEt
> "Jens Smeyer" wrote:
>|||sorry! my mistake
CREATE PROCEDURE sp_AssignActPass (@.CustID varchar(4), @.UserID
varchar(7),@.ActPass varchar(45),@.LastUpdate datetime, @.result int OUTPUT)
AS
UPDATE MemberSET ActPass = @.ActPass, LastUpdate = CURRENT_TIMESTAMP
WHERE CustID = @.CustID AND UserID = @.UserID AND LastUpdate = @.LastUpdate
IF @.@.ROWCOUNT = 0
BEGIN
IF EXISTS(SELECT UserID FROM Member WHERE CustID = @.CustID AND UserID =
@.UserID)
SET @.result = 2
ELSE
SET @.result = 1
END
ELSE
SET @.result = 0
RETURN @.result
GO
hope this will work now
best Regards,
Chandra
---
"wrytat" wrote:
> I get an error message.
> Erro1 170: Line 3: Incorrect syntax near ','.
> Must declare the variable '@.result'.
> Must declare the variable '@.result'.
> Must declare the variable '@.result'.
> Must declare the variable '@.result'.
>
> "Chandra" wrote:
>

returning value from sql to c#

hello,
I have a small problem. i'm adding records into the DB. the primary keyis the company name which is abviously unique. before saving the recordi check in the stored procedure if the company code is unique or not.if unique then the record is added & an output parameter is set to2 & should b returned to the data access layer. if not unique then3 should be returned. but everytime it seems to be returning 2 whetherit is unique or not. can u plz help me? here is the code of the dataaccess layer:
cmd.Parameters.Add("@.Status", SqlDbType.Int);
cmd.Parameters["@.Status"].Value = ParameterDirection.ReturnValue;

//cmd.UpdatedRowSource = UpdatedRowSource.OutputParameters;
cmd.ExecuteNonQuery();
status = (int)cmd.Parameters["@.Status"].Value;

here is the stored procedure:
CREATE PROCEDURE spOrganizationAdd(
@.OrgCode varchar(10),
@.OrgName varchar(50),
@.AddressLine1 varchar(30),
@.AddressLine2 varchar(30),
@.City varchar(15),
@.State varchar(15),
@.Country varchar(15),
@.PinCode varchar(7),
@.Phone varchar(20),
@.Fax varchar(20),
@.Website varchar(30),
@.Email varchar(50),
@.CreatedBy int,
@.LastModifiedBy int,
@.Status INTEGER OUTPUT) AS
BEGIN TRAN
IF EXISTS(SELECT OrgCode FROM tblOrganizationMaster WHERE OrgCode = @.OrgCode)
BEGIN
SET @.Status = 3

END
ELSE
BEGIN
INSERT INTO tblOrganizationMaster VALUES(
@.OrgCode,
@.OrgName,
@.AddressLine1 ,
@.AddressLine2 ,
@.City ,
@.State,
@.Country,
@.PinCode,
@.Phone,
@.Fax ,
@.Website,
@.Email,
@.CreatedBy ,
GETDATE(),
@.LastModifiedBy ,
GETDATE())
SET @.Status = 2
END
IF @.@.ERROR = 0 COMMIT TRAN
ELSE ROLLBACK TRAN

plz reply as soon as possible.

Change this:

cmd.Parameters.Add("@.Status", SqlDbType.Int);
cmd.Parameters["@.Status"].Value = ParameterDirection.ReturnValue;

To this:

cmd.Parameters.Add("@.Status", SqlDbType.Int);
cmd.Parameters["@.Status"].Direction = ParameterDirection.Output;

and then it should work.

Bill

Returning Value from Dynamic query

I want to get the count of rows in the table which match the status. I am writing dynamic query for it..
Create Procedure Dyn_Get_CountByStatus
(
@.TableName varchar(200),
@.Status int
)
as
Begin
Declare @.strQuery varchar(500)
Declare @.count int
set @.strQuery = 'select count(*) from '+@.TableName + 'where status=' + @.Status
set @.count =exec(@.strQuery)
return @.count
End
GO

This query is not working. How can get the desired result using dynamic query

Try using the @.@.rowcount instead....

Create Procedure Dyn_Get_CountByStatus
(
@.TableName varchar(200),
@.Status int
)
as
Begin
Declare @.strQuery varchar(500)
Declare @.count int
set @.strQuery = 'select * from '+@.TableName + 'where status=' + @.Status
exec(@.strQuery)

set @.count =@.@.rowcount
return @.count
End
GO

|||

You can do something like what I show below, BUT.... before you do, you should know that using dynamic SQL in the way you are using it is a VERY BAD IDEA. Suppose someone names a table something like [(select 1 i) T delete clients --] Then if you run this procedure, all rows from the table called [clients] will be deleted! If anyone has permission to create tables in your database on which this procedure will be executed, they can create a maliciously-named table name and wait for someone with elevated permission to run this procedure. You can protect yourself from most dangers fairly well by using QUOTENAME, though there are some truncation questions that can still allow security risks even when QUOTENAME is used. Valuable reading: http://www.sommarskog.se/dynamic_sql.html http://mvp.unixwiz.net/techtips/sql-injection.html -- MODIFY AT YOUR OWN RISK create table clients (i int) insert into clients values (10) insert into clients values (13) go declare @.tn sysname set @.tn = 'clients' declare @.TotalRecords int declare @.sql nvarchar(600) set @.sql = N' select @.TotalRecords = count(*) from '+ quotename(@.tn) + ' where i > @.param' exec sp_executesql @.sql, N'@.param int, @.TotalRecords int OUTPUT', @.TotalRecords = @.TotalRecords OUTPUT, @.param = 11 select @.TotalRecords go -- Steve Kass -- Drew University ashwin_k_s@.discussions.microsoft.com wrote:
> I want to get the count of rows in the table which match the status. I
> am writing dynamic query for it..
>
> Create Procedure Dyn_Get_CountByStatus
> (
> @.TableName varchar(200),
> @.Status int
> )
> as
> Begin
> Declare @.strQuery varchar(500)
> Declare @.count int
> set @.strQuery = 'select count(*) from '+@.TableName + 'where status='
> + @.Status
> set @.count =exec(@.strQuery)
> return @.count
> End
> GO
>
> This query is not working. How can get the desired result using dynamic
> query
>
>
>