query help - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

query help

Author

Topic

aakcse
Aged Yak Warrior

570 Posts

Posted - 2012-08-30 : 14:42:16

The below query was working fine for me to display the required data, now the requirement has changed,

The RAISERROR( @msg,11,1) was used to pass the control to catch block for any existence of the any of the 4 table with col name RAW else run the main query

Now the requirement is change, even if one of the table is not having RAW as the column then the main query should be executed to display result for that table. could any one help me in modifying this block

In short if a table has RAW as col ignore that table and pull data for other tables, earlier in the below query if any of the table has RAW as col then I was ignoring all other table and jumping to catch

Table which has RAW column can be shown in the main query as 'table has Raw col' some thing like this


DECLARE @Col_name VARCHAR(100)
DECLARE @tab_nam VARCHAR(100)
DECLARE @msg VARCHAR(500)
DECLARE @SQLString VARCHAR(3000)
 
 SELECT @Col_name= column_name
    ,@tab_nam=table_name 
 FROM INFORMATION_SCHEMA.COLUMNS WHERE  table_name = 'at_allow' and column_name ='RAW'
 IF @Col_name = 'RAW'
 BEGIN
  SET @msg= 'File ''at.allow'' does not exist.'
  RAISERROR( @msg,11,1)
 END

 SELECT @Col_name= column_name
    ,@tab_nam=table_name 
 FROM INFORMATION_SCHEMA.COLUMNS WHERE  table_name = 'at_deny' and column_name ='RAW'
 IF @Col_name = 'RAW'
 BEGIN
  SET @msg= 'File ''at.deny'' does not exist.'
  RAISERROR( @msg,11,1)
 END

  SELECT @Col_name= column_name
    ,@tab_nam=table_name 
 FROM INFORMATION_SCHEMA.COLUMNS WHERE  table_name = 'cron_allow' and column_name ='RAW'
 IF @Col_name = 'RAW'
 BEGIN
  SET @msg= 'File ''cron.allow'' does not exist.'
  RAISERROR( @msg,11,1)
 END

 
 SELECT @Col_name= column_name
    ,@tab_nam=table_name 
 FROM INFORMATION_SCHEMA.COLUMNS WHERE  table_name = 'cron_deny' and column_name ='RAW'
 IF @Col_name = 'RAW'
 BEGIN
  SET @msg= 'File ''cron.deny'' does not exist.'
  RAISERROR( @msg,11,1)
 END


WITH CTE([UserName]) AS
 (
 SELECT [UserName] AS [UserName]
 FROM at_allow
 UNION
 SELECT [UserName] AS [UserName]
 FROM at_deny
 UNION
 SELECT [UserName] AS [UserName]
 FROM cron_allow
 UNION
 SELECT [UserName] 
 FROM cron_deny
 )
 SELECT [UserName], 
 [Cron Allow]	= CASE WHEN EXISTS(SELECT 1 FROM cron_allow WHERE [UserName] = CTE.[UserName]) THEN 'Yes' ELSE 'No' END,
 [Cron Deny]	= CASE WHEN EXISTS(SELECT 1 FROM cron_deny WHERE [UserName] = CTE.[UserName]) THEN 'Yes' ELSE 'No' END,
 [At Allow]		= CASE WHEN EXISTS(SELECT 1 FROM at_allow WHERE [UserName] = CTE.[UserName]) THEN 'Yes' ELSE 'No' END,
 [At Deny]		= CASE WHEN EXISTS(SELECT 1 FROM at_deny WHERE [UserName] = CTE.[UserName]) THEN 'Yes' ELSE 'No' END 
 FROM CTE
 EXEC (@SQLString)

END TRY
BEGIN CATCH
 SELECT  ERROR_MESSAGE() AS [UserName],'' AS [Cron Allow],'' AS [Cron Deny] ,'' AS  [At Allow],'' AS [At Deny]
END CATCH

-Neil

aakcse
Aged Yak Warrior

570 Posts

Posted - 2012-08-30 : 14:56:41

All the four table could only have one column as [userName] if not RAW. and if not RAW than the data in the [userName] column will have just user name like Mike joe etc..

-Neil

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-30 : 15:36:55

for this requirement why do you need to throw error like this?

isnt it enough to add a loop logic based on INFORMATION_SCHEMA catalog views to get tables without column RAW and just do select columns from them?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

aakcse
Aged Yak Warrior

570 Posts

Posted - 2012-08-31 : 01:11:26

If the table col name is RAW then I need to send this info to f/end that this file is missing... the table with RAW column is created dynamically only when we do not have the files( all 4 tables ) if we have files then a proper table with proper column([UserName]) & proper data is created

Thanks :)

-Neil

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-31 : 10:05:39

quote:
Originally posted by aakcse

If the table col name is RAW then I need to send this info to f/end that this file is missing... the table with RAW column is created dynamically only when we do not have the files( all 4 tables ) if we have files then a proper table with proper column([UserName]) & proper data is created

Thanks :)

-Neil

Again for this i would have simply done check based on information_schema and returned a status bit from procedure. then at front end check status and set appropriate message

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources