Attaching a variable to a SELECT statement

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Attaching a variable to a SELECT statement

Author

Topic

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2005-03-07 : 17:17:40

Hi gang,
Anyone know of a way to do this...
We have an app that connects to SQL and passes filter text to procedures as a variable. Normally the procedure gets written with a SELECT query as a variable like this:

set @SQL='select <something>
from <a table>'
set @SQL_FILTER=' where <something is something>'/*passed from app*/
set @SQL=@SQL+@SQL_FILTER
exec(@SQL)

The filter is passed as varchar, with no quote marks.
This works fine for most everything, but now we have a little problem.
I need to connect the @SQL_FILTER to a subquery,and execute THAT:

select *
from <table>
where <something>=(select <something> from <another table> +@SQL_FILTER)

Even looking at this I'm saying "Nope, ain't happenin'.."
Anybody have ANY ideas?
Andy

There's never enough time to type code right,
but always enough time for a hotfix...

nr
SQLTeam MVY

12543 Posts

Posted - 2005-03-07 : 19:14:42

select @sql = 'select *
from <table>
where <something>=(select <something> from <another table> ' + @SQL_FILTER + ')'
exec (@sql)

You might want
where <something> in (select ....

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2005-03-07 : 19:55:15

Hey nr,
I guess I wasn't as clear as I needed to be; The subquery can't be coupled to the @SQL string and then executed. @SQL is the string that is passed back to Crystal Reports, and the subquery is needed to do an error check inside the procedure. I emailed Crystal to see if they had an answer to this, and they aren't coming up with much.
Maybe there is a better way to do what needs to be done. Below I've posted the actual proc that this is going into, and have highlighted and noted the problem area:


/* ================================================================================= */
CREATE        procedure USER_RPT_TIMETABLE_MAINT 
  @Add          bit,                       -- displays as checkbox
  @Vac		bit,		--displays as checkbox
  @Delete	bit,		--displays as checkbox
  @Expand	bit,			--displays as checkbox
  @RunMode           int,                         -- displays as numeric entry
  @TimeTable_Filter    T_SQL_FILTER,                -- TimeTable Filter
  @OrderBy           T_SQL_QUERY                  -- OrderBy
as

/* Declare local variables */
  declare @ERR_FLG            T_FLG          -- Error message flag
  declare @ERR_MSG            T_ERR_REF      -- Text of error message 
  declare @SQL_I              T_SQL_QUERY    -- Text of inner query 
  declare @SQL                T_SQL_QUERY    -- Text of actual query 

  declare                                    -- All variables 
  @SVC_DAT                  DATETIME,
  @NAM            T_NAM,          -- Servicer name
  @TIME01        T_DOC_NO,       -- 9:00 to 11:00 ticket number
  @TIME04        T_DOC_NO,          -- 10:00 to 12:00 ticket number
  @TIME07        T_DOC_NO,       -- 11:00 to 1:00 ticket number
  @TIME10        T_DOC_NO,        -- 1:00 to 3:00 ticket number
  @TIME13        T_DOC_NO,          -- 2:00 to 4:00 ticket number
  @TIME16        T_DOC_NO          -- 3:00 to 5:00 ticket number

begin

set nocount on

/* Set variables - */
  set @ERR_FLG           = '0'

  set @ERR_MSG           = 'testing the message'

  if @RunMode = 1 
    begin
     
      print '   @Add: ' + cast(@Add as varchar)
      print '   @RunMode: ' + cast(@RunMode   as varchar)
      print '   @Vac: ' + cast(@Vac    as varchar)
      print '   @Delete: ' + cast(@Delete   as varchar)
      print '   @Expand: ' + cast(@Expand    as varchar)
    end

/* ======================================================================================= */
/* Make sure only one parameter is selected at any time				*/
/* If Timetable filter is blank for vacation insert, flag as error                                         */
/* If deleting, check to see that deleted user(s) do not have any calls scheduled; flag as error if yes    */
/* If expanding, check to see if there are any new users to add first; flag as error if yes        */
if @Add=1 and (@Vac=1 or @Delete=1 or @Expand=1)
set @ERR_FLG='5'
if @Vac=1 and (@Add=1 or @Delete=1 or @Expand=1)
set @ERR_FLG='5'
if @Delete=1 and (@Add=1 or @Vac=1 or @Expand=1)
set @ERR_FLG='5'
if @Expand=1 and (@Add=1 or @Vac=1 or @Delete=1)
set @ERR_FLG='5'

if @Vac=1 and coalesce(@Timetable_Filter,'') = ''
 set @ERR_FLG = '1'

if @Delete=1 and coalesce(@Timetable_Filter,'') = ''
 set @ERR_FLG = '6'
if @Expand = 1 and EXISTS(Select nam from SY_USR where sy_usr.is_servicer='Y' and sy_usr.nam not in(select distinct(nam) from timetable))
    set @ERR_FLG = '4'
 
set @SQL='
SELECT *,cast(''none'' as varchar(512)) as ERR_MSG
FROM Timetable
left join SY_USR
on Timetable.nam=SY_USR.nam'

 if coalesce(@Timetable_Filter,'')<> ''
set @SQL=@SQL+' where '+' '+@TimeTable_Filter

set @SQL=@SQL+' and '+' svc_dat>=convert(datetime, convert(varchar(10), getdate(), 120))
 and isnumeric(time01)=1 or isnumeric(time04)=1 or isnumeric(time07)=1
 or isnumeric(time10)=1 or isnumeric(time13)=1
 or isnumeric(time16)=1'



if @ERR_FLG<'1'
exec (@SQL)
 

if @Vac=1  AND @@rowcount>0
set @ERR_FLG='2'

if @Delete = 1 and @@rowcount>0
    set @ERR_FLG = '3'


/* ======================================================================================= */
/* Set SQL inner syntax and Update Timetable accordingly.  SQL_I is the inner query to run.         */

  if @ERR_FLG <> '0'
  begin
    set @SQL_I = '
      select 
      null    as SVC_DAT,
      null    as NAM,
      null    as Time01,
      null    as Time04,
      null    as Time07, 
      null    as Time10, 
      null    as Time13,
      null    as Time16, '

    if @ERR_FLG = '1'
    set @SQL_I = @SQL_I + 
      'cast(''Vacation time cannot be created: ' + 
      ' No user or times selected. ''
      as varchar(512))                                             as ERR_MSG'

    if @ERR_FLG = '2'
    set @SQL_I = @SQL_I + 
      'cast(''Vacation cannot be applied: ' + 
      'User still has calls scheduled.''
      as varchar(512))                                             as ERR_MSG'

 if @ERR_FLG = '3'
    set @SQL_I = @SQL_I + 
      'cast(''User cannot be deleted: ' + 
      'User still has calls scheduled.''
      as varchar(512))                                             as ERR_MSG'

    if @ERR_FLG = '4'
    set @SQL_I = @SQL_I + 
      'cast(''Timetable cannot be expanded: ' + 
      'There are New Users to add before expansion ''
      as varchar(512))                                             as ERR_MSG'

    if @ERR_FLG = '5'
    set @SQL_I = @SQL_I + 
      'cast(''Multiple Operations selected: ' + 
      'You must do 1 operation at a time ''
      as varchar(512))                                             as ERR_MSG'

    if @ERR_FLG = '6'
    set @SQL_I = @SQL_I + 
      'cast(''User cannot be deleted: ' + 
      ' No user selected. ''
      as varchar(512))                                             as ERR_MSG'

  end 
  else 
  begin
    set @SQL_I = '
      select 
     SVC_DAT, NAM,Time01,Time04, Time07,Time10,Time13,Time16,
      cast(''None'' as varchar(512))                               as ERR_MSG '

     set @SQL_I = @SQL_I + '
      from TimeTable'
    



    if  coalesce(@Timetable_Filter,'')<>''
      set @SQL_I = @SQL_I + ' where ' + @TimeTable_Filter

    if @RunMode=0
      set @SQL_I = @SQL_I + ' and 1=0 '

    set @SQL_I = @SQL_I + ' group by SVC_DAT,NAM,Time01,Time04,Time07,
Time10,Time13,Time16 '
  end

/* ======================================================================================= */
/* Set SQL syntax using inner query.                                                       */
/* Inner query is used to allow ordering on any Timetable index for now, may add SY_USR to this in future       */
  set @SQL = '
select *
from 
(
' + @SQL_I

  set @SQL = @SQL + '
) Query1
left join
(
  Select * from SY_USR
) SY_USR
on Query1.NAM = SY_USR.NAM
'
  if coalesce(@OrderBy,'') <> ''
    set @SQL = @SQL + ' order by ' + @OrderBy
  else
    set @SQL = @SQL + ' order by SY_USR.USR_ID '

/* ======================================================================================= */
/* Create Cursor to process our updates when doing vacations. This could be done without the cursor          */
if @Vac=1
  begin
    exec ('declare TT_CURSOR cursor for '+@SQL_I)
    open TT_CURSOR
    fetch next from TT_CURSOR 
      into @SVC_DAT, @NAM, @TIME01, @TIME04, @TIME07, @TIME10, @TIME13,@TIME16, @ERR_MSG

    while @@FETCH_STATUS = 0
    begin

/* ======================================================================================= */
/* Insert vacation time  */
       update Timetable
           set Time01='VAC',Time04='VAC',Time07='VAC',
Time10='VAC',Time13='VAC',Time16='VAC'
        from timetable
where svc_dat=@svc_dat and NAM = @NAM

      fetch next from TT_CURSOR 
        into @SVC_DAT, @NAM, @TIME01, @TIME04, @TIME07, @TIME10, @TIME13,@TIME16, @ERR_MSG
    end
    close TT_CURSOR
    deallocate TT_CURSOR
  end
/* =======================================================================================*/
/* Delete servicers and update SY_USR.IS_SERVICER flag		*/
if @Delete=1
begin
set @SQL_I='
update SY_USR
set IS_SERVICER=''N''
from SY_USR
join TimeTable
on SY_USR.nam=Timetable.nam'
set @SQL_I=@SQL_I+' where svc_dat>=convert(datetime, convert(varchar(10), getdate(), 120))'
set @SQL_I=@SQL_I+' and '
set @SQL_I=@SQL_I+@TimeTable_Filter
exec (@SQL_I)
set @SQL_I='
Delete from Timetable'
set @SQL_I=@SQL_I+' where '
set @SQL_I=@SQL_I+@TimeTable_Filter
exec(@SQL_I)
end

/* ======================================================================================= */
/* Add new Servicers to TimeTable                      */
IF @Add=1
begin
EXEC PR_UPDATETIMETABLE
      end
/*======================================================================================== */
/* Expand existing Timetable				*/
   if @Expand=1
begin
exec pr_Createtimetable
end
/* ======================================================================================= */
  if @RunMode = 1 
    print @SQL

/* ======================================================================================= */
  execute(@SQL)

end
GO

The red text needs to be done outside of @SQL if at all possible. I don't know, this looks like it will need a rewrite of much more before we're done...
Andy

There's never enough time to type code right,
but always enough time for a hotfix...

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-03-07 : 20:39:16

Unless I'm missing something, you will have to DECLARE another string inside the @sql and populate it before passing it to the EXEC(@sql). Make sense? Remember that no variable exists outside of its immediate context unless it's a global. Therefore, for your statement to work, you'll need to DECLARE another variable and execute the entirety of what's needed to populate that variable inside of the EXEC(@sql). We went through this in a "global marketing import" procedure. It's a mess because you're building loops inside of loops. It's also extremely inefficient, as many times it requires entire processes to be executed multiple times for the nesting levels.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2005-03-07 : 21:29:25

Hey Derrick,
Yup, I think you hit it right on the head. This thing has been unholy at best, and I have the "loop in a loop" process is bad,bad,bad. I may have stumbled on a very dirty but functional fix:
The way the proc is written right now, when there are calls still scheduled for a user those rowsets are returned to Crystal from the first @SQL (the one We're trying to get rid of), which is a good thing. If there are no rows returned from the first @SQL, then nothing is returned to Crystal, which is a problem (sorta). It is more important to see calls that are holding up the setting of vacation or the deletion of a user than to see all the rows that are successful (Just what would a successful DELETE look like, anyhow?). I can make Crystal print all kinds of stuff off the parameter flags, so I can build a workaround without getting into the multiple loop quandry.
I would rather do one of those nasty 40-table cross joins than bastardize this kind of stuff, buuuuuuuutt.......

There's never enough time to type code right,
but always enough time for a hotfix...

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2005-03-08 : 07:17:19

Here's another thought about this:
would it be possible to JOIN something to this query:

set @SQL='
SELECT *,cast(''none'' as varchar(512)) as ERR_MSG
FROM Timetable
left join SY_USR
on Timetable.nam=SY_USR.nam'

 if coalesce(@Timetable_Filter,'')<> ''
set @SQL=@SQL+' where '+' '+@TimeTable_Filter

set @SQL=@SQL+' and '+' svc_dat>=convert(datetime, convert(varchar(10), getdate(), 120))
 and isnumeric(time01)=1 or isnumeric(time04)=1 or isnumeric(time07)=1
 or isnumeric(time10)=1 or isnumeric(time13)=1
 or isnumeric(time16)=1'

Such that we could do our ISNUMERIC test and still return the same query WITHOUT ISNUMERIC? In other words, can the above query return this:

set @SQL='
SELECT *,cast(''none'' as varchar(512)) as ERR_MSG
FROM Timetable
left join SY_USR
on Timetable.nam=SY_USR.nam'

 if coalesce(@Timetable_Filter,'')<> ''
set @SQL=@SQL+' where '+' '+@TimeTable_Filter

set @SQL=@SQL+' and '+' svc_dat>=convert(datetime, convert(varchar(10), getdate(), 120))

and still do our isnumeric tests?
Andy

There's never enough time to type code right,
but always enough time for a hotfix...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources