DECLARE @SQL Varchar(Max) DECLARE @DESCR Varchar(Max) -- Customer enters description into @Descr SET @SQL = 'Update TableName SET FieldName=''' + @DESCR + ''' WHERE ID=123' The problem is when the customer enters an apostrophe into the @Descr variable. Q: In Microsoft SQL Server 2005, how do I replace all apostrophies with double apo...
I'm trying to do the following, If a user the enters the term "IP Address Text" into my search box then I want the following SQL to be generated: SELECT * FROM tblComments WHERE tblComments.Text LIKE '%IP%' OR tblComments.Text LIKE '%Address%' OR tblComments.Text LIKE '%Text%' Obviously the number of words entered is going to be diffe...
Hi, i want to accomplish this: update @sourceDatabase.dbo.PredictedPrices and then set @sourceDatabase as a variable. But i'm not allowed? Incorrect syntax near '.'. Is there another way? ...
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...
Below mentioned stored procedure is giving error while creating Msg 156, Level 15, State 1, Procedure crosstab, Line 23 Incorrect syntax near the keyword 'pivot'. Can anyone please tell me the mistake? Below is the script: CREATE PROCEDURE crosstab @select varchar(8000), @sumfunc varchar(100), @pivot varchar(100), @table varchar(...
Hey all, i was wondering if there was a way to do this in MS SQL Server 2005: DECLARE @theDate varchar(60) SET @theDate = '''2010-01-01'' AND ''2010-08-31 23:59:59''' SELECT AdministratorCode, SUM(Total) as theTotal, SUM(WOD.Quantity) as theQty, AVG(Total) as avgTotal, (SELEC...
Hi, Executing dynamic SQL as follows in Stored Procedure: DECLARE @sqlCommand nvarchar(1000) DECLARE @city varchar(75) SET @city = 'London' SET @sqlCommand = 'SELECT COUNT(*) FROM customers WHERE City = @city' EXECUTE sp_executesql @sqlCommand, N'@city nvarchar(75)', @city = @city How do I use the count(*) column value as return valu...
I have a select statement that returns a table full of SELECT statements (It goes through every column in every table and creates a select to find if that column contains any bad data). I need to take this table full of SELECT statements, execute them, and see if any of them return rows. If the count(*) > 0, then I want to print out ...
Hi, I have the following query: create proc [dbo].GetCustById as DECLARE @sql nvarchar(500) DECLARE @Param nvarchar(200) SET @sql = 'select @columnName from customer where custId = @custId' SET @Param = N'@columnName varchar(10), @custId int' EXEC sp_executesql @sql, @Param , @columnName = 'Address1', @custId = '42' But it always r...
I'm attempting to construct a Django application that models an existing set of tables. These tables all have the same fields, plus custom fields per table. What I'm wanting to do is model this structure, and have records save to a particular table based on what table model they are attached to. These tables can be created quite often,...
I have a database where the primary user logging into the database has no permissions to any of the tables directly. THe user needs to execute stored procedures which have been explicitly granted to it. This covers any straightforward CRUD operation that is needed. But now, I have a need to execute SQL Dynamically but I want to maintain ...
Hi, Im currently making a very simple WebApp on a Website using ASP.NET Framework using C# in Visual Studio 2010. The website will connect to my SQL EXPRESS server running on my laptop (Its all locally based) I have a table defined as below CREATE TABLE Users( userName varchar(50), email varchar(50), firstName varchar(50), lastNa...
I added my local server pblack as linked server in SQL Server 2008 R2 ---1) EXEC master.dbo.sp_addlinkedserver @server = N'pblack', --'pblack' is my localhost @srvproduct=N'SQL Server' and executed successfully 2) and 3): --2) sp_MSforeachtable @command1="EXEC sp_spaceused '?'" --3) SELECT * INTO #te...
Is there a PL/SQL function or general technique to quote unqualified identifiers (e.g., mytable) for use in a dynamically constructed SQL query? How about partially or fully qualified identifiers (a.b@c)? Consider this contrived example: CREATE PROCEDURE by_the_numbers(COL_NAME VARCHAR, INTVAL INTEGER) IS ... BEGIN -- COL_NAME is ...