sql query with error

did you do as I recommended and copy all of that code into a new query and try it with

SELECT @SQL

instead of:

EXEC (@SQL)

The error is stating that one of the fields you are trying to update / create is larger then what was specified when creating the table. For example lets say you have a field named FirstName and it has a field width of 25 characters and the string you are sending is 26 you will get an error as stated above. Check you field width from what you are sending in.

Fernando,
I am not updating any field .Its just an extract .
Please see the entire stored Procedure.

Thanks

Well, one error I can see is lacking a space in the beginning of 'UNION ALL '  ie make it  '   union all   '

And no need to do : SET @SQL = @SQL

Do you get the error if specifying something for @value2 or not ?

Because if you are, you end up with "[Year_Ref] =2018union all"

The other potential error is not matching the datatype of the column in the where clause to the value of the where clause.

It seems that @value is always being cast as varchar, but not much validation or type casting to match the datatype of the column.

        SET @SQL = @SQL + ' FROM  [' + @tableName + '] '   + ' WHERE  [' + @ColumnName1 + ']  = ''' +   '' +  @Value +''''+ ' AND  [Year_Ref] =' + convert(varchar,@Year_Ref)

Same again for the second set.

Which (if casting all as NULL) doesnt add anything materialy to the query..

Just before the EXEC (@SQL) put a PRINT @SQL and please show us that PRINT result.

And... those column names seem to be very specific to the table, so, wondering why parameterising the tables....

Can you please post the results of

select column_name, ordinal_position, is_nullable, data_type, character_maximum_length
from information_schema.columns
where TABLE_NAME = @tableName

And for the second table as well ?

Thanks Mark... will try and brb

Why is UNIONALL returning 2 rows, is there any way to make it a single row?

Thanks

Thanks Mark! I am gonna post a comment ....just let me read yours carefully

Mark,
@TableName and @Tablename2 cannot be same. They are 2 different tables.
When I run your query I get a error :
 Sql column 'RequestedBy' does not belong to tablename2
it is a error specifying all columns of table1

Cheers

Mark,
This SP works fine but returns 2 rows.

ALTER PROCEDURE  [dbo].[ Wot]
(
     @tableName varchar(MAX) = null,
	 @ColumnName1 varchar(MAX) = null,
	 @Value varchar(MAX) = null,
	 --Table2
	 @tableName2 varchar(MAX) = null,
	 @ColumnName2 varchar(MAX) = null,
	 @Value2 varchar(MAX) = null


)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;  	
	
	   declare @SQL varchar(max) = null
	  -- NONCLUSTERED INDEX [NIX__UNQ__UID_]
	   DECLARE @Year_Ref smallint
		SET @Year_Ref = (SELECT [Year_Ref] FROM YEAR_LOOKUP  WHERE [Year_no]= YEAR(GETDATE()))

	

	--SET @SQL = 'SELECT Authority,Date,Ref,CorresType, Comments,SentTo,Regarding,RoomTypeRouting,RoomTypeRouting2,RoomTypeRouting3,RoomTypeRouting4,Faxnumber,
	           -- Guestname,ConfirmationNumber,ArrivalDate,ArrivalDate2,ArrivalDate3,ArrivalDate4,DepartureDate,DepartureDate2,DepartureDate3,DepartureDate4,Attention,Roomno'
		
	SET @SQL = '  SELECT TOP 1 RequestedBy,Authority,[Date],Ref,CorresType, Comments,SentTo,Regarding,RoomTypeRouting,RoomTypeRouting2,RoomTypeRouting3,RoomTypeRouting4,RoomTypeRouting5,RoomTypeRouting6,Faxnumber,
	      Guestname,ConfirmationNumber , ArrivalDate , ArrivalDate2,  ArrivalDate3,ArrivalDate4, ArrivalDate5,  ArrivalDate6 ,Attention,Roomno, Roomno2,Roomno3,Roomno4,Roomno5,Roomno6,null as  Route,CAST(null as date) as DateFrom,  CAST(null as date) as DateTo,
		(CASE
                 WHEN Extendedto IS NULL THEN DepartureDate
                 ELSE Extendedto
        END) AS DepartureDate,
		(CASE
                  WHEN Extendedto2 IS NULL THEN DepartureDate2
                  ELSE Extendedto2
        END) AS DepartureDate2,
		(CASE
                  WHEN Extendedto3 IS NULL THEN DepartureDate3
                  ELSE Extendedto3
        END) AS DepartureDate3,
			(CASE
                  WHEN Extendedto4 IS NULL THEN DepartureDate4
                  ELSE Extendedto4
        END) AS DepartureDate4,
		(CASE
                  WHEN Extendedto5 IS NULL THEN DepartureDate5
                  ELSE Extendedto5
        END) AS DepartureDate5,
		(CASE
                  WHEN Extendedto6 IS NULL THEN DepartureDate6
                  ELSE Extendedto6
        END) AS DepartureDate6' 
	 
	 
	  SET @SQL = @SQL + ' FROM  [' + @tableName + '] '   + ' WHERE  [' + @ColumnName1 + ']  = ''' +   '' +  @Value +''''+ ' AND  [Year_Ref] =' + convert(varchar,@Year_Ref)
	
	  SET @SQL = @SQL 
	  
	  

	  IF  @Value2  <> ''		
	  SET @SQL = @SQL +  'union all '
	  IF  @Value2  <> ''		
	 SET @SQL = @SQL +  ' SELECT TOP 1 null as RequestedBy ,null as Authority,CAST(NULL as date),null as Ref,null as CorresType, null as Comments,null as SentTo,null as Regarding,null as RoomTypeRouting,null as RoomTypeRouting2,null as RoomTypeRouting3,null as RoomTypeRouting4,null as RoomTypeRouting5,null as RoomTypeRouting6,null as Faxnumber,
	      null as Guestname,null as ConfirmationNumber,	 CAST(null as date) as ArrivalDate ,CAST(null as date) as ArrivalDate2,CAST(null as date) as  ArrivalDate3,CAST(null as date) as ArrivalDate4,CAST(null as date) as ArrivalDate5,CAST(null as date) as  ArrivalDate6,null as Attention,null as Roomno, null as Roomno2,null as Roomno3, null as Roomno4,null as Roomno5,null as Roomno6, Route, CAST(null as date) as DateFrom, CAST(null as date) as DateTo, CAST(null as date) as   DepartureDate,CAST(null as date) as  DepartureDate2,
		  CAST(null as date) as   DepartureDate3, CAST(null as date)  as  DepartureDate4, CAST(null as date) as   DepartureDate5, CAST(null as date) as DepartureDate6' 	
	  IF  @Value2  <> ''		 
	  SET @SQL = @SQL + ' FROM  [' + @tableName2 + '] '   + ' WHERE  [' + @ColumnName2 + ']  = ''' +   '' +  @Value2 +''''	
	  SET @SQL = @SQL  
	  
	  
	   	
	  
	  EXEC (@SQL)	
		   
	
END

Select allOpen in new window

Well yes, you are telling it to extract 2 rows.

I have no idea what you are trying to achieve, what the parameters are - at least share those.

You are SELECTING TOP 1  but have no order by. So it will be a (random) choice determined by SQL server, not by you. You need to have an Order By in there.

You cannot specify a parameter for @values or @value2 to exclude NULL because you are encapsulating those @value(2) in single quotes.

So even it you wanted t exclude NULL values from the second query, there is no way to specify : IS NOT NULL

And what are you trying to extract from the second query ? As far as I can tell, it does seem to me that every column is being cast as NULL.

Reposting the same query with "but returns 2 rows" - we dont even know if @value2 is being used - it isnt being checked for NULL just > ''

So at the very least you should be checking : IF ISNULL(@value2,'') > ''

Can you show us how you are executing the procedure ? (use T1 and T2 as table names if you want to obfuscate)

You still need a leading space in 'union all ' it MUST be : '  union all  '

Please re-read some of the other comments above. And while I realise that what is being posted, isnt necessarily what is really happening, we can only respond to what is being posted.

So, please put a PRINT @SQL before the EXEC (@SQL) and post the output from the messages tab.

>> "it is a error specifying all columns of table1 "

Yes, expected as much and said so : "Not least of which is the column name dependency on the parameters @TableName and @Tablename2"
The idea was you could replace the columns you need to based on the dependency on @TableName2. The point was that part of the query will not return anything meaningful if you cast every column as NULL.

Thanks Mark,
I will be right back

Sorry mark , For the late reply.. was away for a day.
Yes,  I am trying to select few columns from TABLE1 and few from TABLE2.
Thats what the requirement is,
There are no common columns in between the 2 tables.

Can you suggest anything?


Thanks

Thanks Mark!

Mark,
Please find the sample file.
Mark, I cannot change the column names or add any additional ones .

Cheers
TestingSqlUnion.xlsx

Well, it doesnt look like a UNION query...

Is there anything that relates Table1 rows with suffix 1 to Table2 rows also with suffix 1 ?

Or, is it simply 1 for 1 ?

What if Table1 had an extra 2 rows ? Or table2 had 3 extra?

You wouldnt want Invoice1 to go against name6 when it really belongs to name4....  So, must be something to connect names to invoices....

Thanks Mark... will get back ..on this

Thanks  Mark,
I followed your instructions and created a common column. and it worked great!
Thanks for all you help .
Sorry bud was little off track as got a bit delayed.

Thanks

No problems, was prepared to dig deeper with you. Very happy to hear you found a common columns. Makes life a lot easier :)

Yes Mark... just following your advise... Cheers:>