Why Dynamic Sql written in procedure working and returning data on development server but not returning on production server

Question

Why Dynamic Sql written in procedure working and returning data on development server but not returning on production server

Answer 1

Dynamic SQL is often data driven, either the data used to create the query or the data being queried. What is often helpful is something like this in both dev and prod: DECLARE @sql VARCHAR(MAX), @id int SELECT @sql = 'select * from mytable where id = ' + CAST(@id AS VARCHAR(10)) SELECT @sql -- exec(@sql) Instead of executing the code, print it and make sure it is well formed, and run it separately.

Answer 2

It's not working on the production server, because your code is making some assumption that is not true on the production server.

What assumption? Well, since you did not provide any details, we cannot say more.