|
|
Posted by meendar on 05/22/07 12:30
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.
[Back to original message]
|