using sp_executesql

  • I have the following table:

    CREATE TABLE [COUNTS] (

     [sDatabase] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [Universe] [int] NULL ,

     [VoiceMessages] [int] NULL ,

     [AMMessages] [int] NULL ,

     [Disconnects] [int] NULL

    ) ON [PRIMARY]

    GO

    INSERT [COUNTS] (sDatabase, universe, voicemessages, ammessages, disconnects)

    VALUES ('voicenet_NGEV1', 57794,0,0,0)

    INSERT [COUNTS] (sDatabase, universe, voicemessages, ammessages, disconnects)

    VALUES ('voicenet_AT1037', 5117,5029,55,33)

    I need to execute the following query:

    SELECT @Universe = Universe

         @VM = VoiceMessages,

         @AM = AMMessages,

         @di = Disconnects

    FROM COUNTS

    WHERE sDatabase = @sDatabase

    I need to execute this query dynamically because I populate @sDatabase from another table.

    I know I can use sp_executesql, but I can't get it to work.

    Does anyone know how to do this?

    Thanks,

    Ninel

  • Can you explain why you need to do this dynamically?  Just because the information is from another table should not mean you need to use Dynamic SQL. 

     

    DECLARE @COUNTS TABLE(

              sDatabase varchar(30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

              Universe integer NULL,

              VoiceMessages integer NULL,

              AMMessages integer NULL,

              Disconnects integer NULL)

    INSERT INTO @COUNTS( sDatabase, universe, voicemessages, ammessages, disconnects)

    VALUES( 'voicenet_NGEV1', 57794,0,0,0)

    INSERT INTO @COUNTS( sDatabase, universe, voicemessages, ammessages, disconnects)

    VALUES( 'voicenet_AT1037', 5117,5029,55,33)

    DECLARE @sDatabase varchar(30),

                    @Universe integer,

                    @VM integer,

                    @AM integer,

                    @di integer

    SET @sDatabase = 'voicenet_NGEV1'

    SELECT @Universe = Universe,

         @VM = VoiceMessages,

         @AM = AMMessages,

         @di = Disconnects

    FROM @COUNTS

    WHERE sDatabase = @sDatabase

    SELECT @Universe AS '@Universe', @VM AS '@VM', @AM AS '@AM', @di AS '@DI'

    I wasn't born stupid - I had to study.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply