|
|
Posted by Dan Guzman on 05/23/07 02:21
I'm glad I was able to help.
--
Dan Guzman
SQL Server MVP
"meendar" <askjavaprogrammers@gmail.com> wrote in message
news:1179837037.793991.296140@b40g2000prd.googlegroups.com...
> On May 22, 3:33 pm, "Dan Guzman" <guzma...@nospam-
> online.sbcglobal.net> wrote:
>> > I just need to get two fields from a table and manipulate the results
>> > in next query of a procedure.I planned to code like what you see
>> > below,
>>
>> Why not use a single query? For example:
>>
>> CREATE PROCEDURE dbo.marks1
>> AS
>>
>> SELECT *
>> FROM dbo.marksetting
>> JOIN dbo.mark ON
>> mark.registerno = marksetting.registerno AND
>> mark.subjectcode = marksetting.subjectcode;
>> GO
>>
>> To answer your question, if the first query returns no more than a single
>> row, you could assign the result values to variables for use in the
>> second
>> query:
>>
>> CREATE PROCEDURE dbo.marks1
>> AS
>>
>> DECLARE
>> @registerno int,
>> @subjectcode int
>>
>> SELECT
>> @registerno = registerno,
>> @subjectcode = subjectcode
>> FROM dbp.mark;
>>
>> SELECT *
>> FROM dbo.marksetting
>> WHERE
>> registerno = @registerno AND
>> subjectcode = @subjectcode;
>> GO
>>
>> If the first query might return more than one row, you could store the
>> result in a temp table or variable for use in the second query:
>>
>> CREATE PROCEDURE dbo.marks1
>> AS
>>
>> DECLARE @results TABLE
>> (
>> registerno int,
>> subjectcode int
>> );
>>
>> INSERT INTO @results
>> SELECT
>> registerno,
>> subjectcode
>> FROM dbo.mark;
>>
>> SELECT *
>> FROM dbo.marksetting
>> JOIN @results AS r ON
>> marksetting.registerno = r.registerno AND
>> marksetting.subjectcode = r,subjectcode;
>> GO
>>
>> --
>> Hope this helps.
>>
>> Dan Guzman
>> SQL Server MVP
>>
>> "meendar" <askjavaprogramm...@gmail.com> wrote in message
>>
>> news:1179827614.563723.255190@b40g2000prd.googlegroups.com...
>>
>>
>>
>>
>>
>> > Hi to all,
>>
>> > I just need to get two fields from a table and manipulate the results
>> > in next query of a procedure.I planned to code like what you see
>> > below,
>>
>> > create procedure marks1
>> > as
>> > @ sql1 as varchar(50)
>>
>> > @ sql1=select registerno ,subjectcode from mark;
>>
>> > begin
>>
>> > select * from marksetting where registern...@sql1.registerno' and
>> > subjectcod...@sql1.subjectcode';
>>
>> > end
>>
>> > can it be possible to get the results as shown in the code? else
>> > propose an alternative for this scenario.
>>
>> > Thanks in Advance.- Hide quoted text -
>>
>> - Show quoted text -
>
> Thanks Dan!
>
> Indeed it was very helpful to me.
>
Navigation:
[Reply to this message]
|