Sql Query

Question

I have tables in database from TableA to TableZ that is 26 Tables. I was asked a question in an interview if Suppose a string comes as 'Abcdef' then its first character is A A so it should be inserted into TableAif Suppose first character is B then it should be inserted into TableB I wrote it like this Declare @Name varchar(50)@var varchar(50)@tbl varchar(50) Set @Name = 'ABCDEFG' Set @var = Substring(@Name11) if(@var = 'a') insert into tblA (Name) values (@Name) but this is the wrong way the interviewer told me there will be a need to write 26 times if else.Please do let me know how to solve the above question as I am stuck on it.

Aniket Narvankar · Answer

Declare @Name varchar(50) Declare @var varchar(50) Declare @Sql nchar(200) Set @Name = 'BACDEFG' Set @var = Substring(@Name11) Set @Sql = N'insert into tbl' + @var + '(Name) values (@Name)' EXEC sp_executesql @SqlN'@Name varchar(50)'@Name This worked.Thanks I got the answer

pritaeas · Answer

Missing declare? Declare @Sql nvarchar(200)

Aniket Narvankar · Answer

@pritaeas It is giving me error Procedure sp_executesql Line 1 Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'. Not understanding how to resolve this

pritaeas · Answer

I think he wants you to use EXECUTE sp_executesql You can pass it a query as string which it will execute. So something like this (untested): Declare @Name varchar(50)@var varchar(50)@tbl varchar(50) Set @Name = 'ABCDEFG' Set @var = Substring(@Name11) Set @Sql = 'insert into tbl' + @var + ' (Name) values (@Name)' EXECUTE sp_executesql @Sql Read more here: https://msdn.microsoft.com/en-us/library/ms188001.aspx