When dealing with debugging queries using Profiler and SSMS, its pretty common for me to copy a query from Profiler and test them in SSMS. Because I use parameterized sql, my queries are all sent as exec sp_executesql queries. exec sp_executesql N'/*some query here*/', N'@someParameter tinyint', @ someParameter =2 I'll take this ...
Trying to update a table on a linked server (SQL 2000/2005) but my server name will not be known ahead of time. I'm trying this: DECLARE @Sql NVARCHAR(4000) DECLARE @ParamDef NVARCHAR(4000) DECLARE @SERVER_NAME VARCHAR(35) SET @Sql = 'UPDATE @server_name_param.dba_sandbox.dbo.SomeTable SET SomeCol=''data''' SET @ParamDef = N'@server_n...
I am trying to use sp_executesql to prevent SQL injection in SQL 2005, I have a simple query like this: SELECT * from table WHERE RegionCode in ('X101', 'B202') However, when I use sp_executesql to execute the following, it doesn't return anything. Set @Cmd = N'SELECT * FROM table WHERE RegionCode in (@P1)' SET @ParamDefinition = N'...
Hi, SET @whereCond = @whereCond + ' AND name LIKE ''%'' + @name + ''%''' Is there something wrong here? After I generate where condition, I execute it with sp_executesql, but I did get anything. When I SELECT the same thing without sp, it's ok. How to use LIKE in sp_executesql? Can you bring some examples, please? Thank you. UPDA...
UPDATE : This is what I did - set @dyn_sql = ' select @UserName=UserName from ( select E.ID as EmployeeID, E.UserName as Username ...
I have a database where all access is controlled by stored procedures. The DBA would like to avoid giving users direct read/write access to the underlying tables, which I can understand. Hence all updating and selecting of data is done via stored procedures. Basically he has created one role that has EXECUTE permissions to all the stored...