Getting Result of query inside another query

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • meendar
    #1

    Getting Result of query inside another query


    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 registerno='@sq l1.registerno' and
    subjectcode='@s ql1.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.

    Tags: None
    • Dan Guzman
      #2
      Re: Getting Result of query inside another query

      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.reg isterno AND
      mark.subjectcod e = marksetting.sub jectcode;
      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.reg isterno = r.registerno AND
      marksetting.sub jectcode = r,subjectcode;
      GO

      --
      Hope this helps.

      Dan Guzman
      SQL Server MVP

      "meendar" <askjavaprogram mers@gmail.comw rote in message
      news:1179827614 .563723.255190@ b40g2000prd.goo glegroups.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 registerno='@sq l1.registerno' and
      subjectcode='@s ql1.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.
      >

        Comment

        • meendar
          #3
          Re: Getting Result of query inside another query

          On May 22, 3:33 pm, "Dan Guzman" <guzma...@nospa m-
          online.sbcgloba l.netwrote:
          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.reg isterno AND
          mark.subjectcod e = marksetting.sub jectcode;
          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.reg isterno = r.registerno AND
          marksetting.sub jectcode = r,subjectcode;
          GO
          >
          --
          Hope this helps.
          >
          Dan Guzman
          SQL Server MVP
          >
          "meendar" <askjavaprogram m...@gmail.comw rote in message
          >
          news:1179827614 .563723.255190@ b40g2000prd.goo glegroups.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...@sq l1.registerno' and
          subjectcod...@s ql1.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.

            Comment

            • meendar
              #4
              Re: Getting Result of query inside another query

              On May 22, 3:33 pm, "Dan Guzman" <guzma...@nospa m-
              online.sbcgloba l.netwrote:
              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.reg isterno AND
              mark.subjectcod e = marksetting.sub jectcode;
              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.reg isterno = r.registerno AND
              marksetting.sub jectcode = r,subjectcode;
              GO
              >
              --
              Hope this helps.
              >
              Dan Guzman
              SQL Server MVP
              >
              "meendar" <askjavaprogram m...@gmail.comw rote in message
              >
              news:1179827614 .563723.255190@ b40g2000prd.goo glegroups.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...@sq l1.registerno' and
              subjectcod...@s ql1.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.

                Comment

                • Dan Guzman
                  #5
                  Re: Getting Result of query inside another query

                  I'm glad I was able to help.

                  --
                  Dan Guzman
                  SQL Server MVP

                  "meendar" <askjavaprogram mers@gmail.comw rote in message
                  news:1179837037 .793991.296140@ b40g2000prd.goo glegroups.com.. .
                  On May 22, 3:33 pm, "Dan Guzman" <guzma...@nospa m-
                  online.sbcgloba l.netwrote:
                  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.reg isterno AND
                  > mark.subjectcod e = marksetting.sub jectcode;
                  >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.reg isterno = r.registerno AND
                  > marksetting.sub jectcode = r,subjectcode;
                  >GO
                  >>
                  >--
                  >Hope this helps.
                  >>
                  >Dan Guzman
                  >SQL Server MVP
                  >>
                  >"meendar" <askjavaprogram m...@gmail.comw rote in message
                  >>
                  >news:117982761 4.563723.255190 @b40g2000prd.go oglegroups.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...@sq l1.registerno' and
                  subjectcod...@s ql1.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.
                  >

                    Comment

                    Previous template Next
                    Working...