Error Code: 1054. Unknown column ‘SPA’ in ‘where clause’ This happens when you do not enclose input string within quotes, and SQL engine tries to identify it as a column in the table being queried. But it fails as it can’t find it. But what happens when it finds such column? It fetches results when … Read more
Here is a simple example: EXEC sp_executesql @sql, N’@p1 INT, @p2 INT, @p3 INT’, @p1, @p2, @p3; Your call will be something like this EXEC sp_executesql @statement, N’@LabID int, @BeginDate date, @EndDate date, @RequestTypeID varchar’, @LabID, @BeginDate, @EndDate, @RequestTypeID
EDIT: I haven’t tried this, so I can’t vouch for it! And you already know you shouldn’t be doing this, so please don’t do it. BUT… Try looking here: http://sqlblog.com/blogs/denis_gobo/archive/2008/05/08/6703.aspx The key bit is this bit which I have attempted to tweak for your purposes: DECLARE @SQL varchar(500) SELECT @SQL = ‘osql -S’ +@@servername +’ … Read more
Getting started with stored procedure syntax in MySQL (using the terminal): 1. Open a terminal and login to mysql like this: el@apollo:~$ mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. mysql> 2. Take a look to see if you have any procedures: mysql> show procedure status; … Read more
None whatsover. They are synonyms supplied to make your code more readable: FUNCTION f IS … CREATE VIEW v AS SELECT …
Something like this should do the trick (However, read after the snippet for more info) CREATE PROCEDURE GetFilteredData() BEGIN DECLARE bDone INT; DECLARE var1 CHAR(16); — or approriate type DECLARE var2 INT; DECLARE var3 VARCHAR(50); DECLARE curs CURSOR FOR SELECT something FROM somewhere WHERE some stuff; DECLARE CONTINUE HANDLER FOR NOT FOUND SET bDone = … Read more
The default collation for stored procedure parameters is utf8_general_ci and you can’t mix collations, so you have four options: Option 1: add COLLATE to your input variable: SET @rUsername = ‘aname’ COLLATE utf8_unicode_ci; — COLLATE added CALL updateProductUsers(@rUsername, @rProductID, @rPerm); Option 2: add COLLATE to the WHERE clause: CREATE PROCEDURE updateProductUsers( IN rUsername VARCHAR(24), IN … Read more
NOTE that this is a general look at stored procedures not regulated to a specific DBMS. Some DBMS (and even, different versions of the same DBMS!) may operate contrary to this, so you’ll want to double-check with your target DBMS before assuming all of this still holds. I’ve been a Sybase ASE, MySQL, and SQL … Read more
I realize this has already been marked as answered, but we used to do it like this: IF NOT EXISTS (SELECT * FROM sys.objects WHERE type=”P” AND OBJECT_ID = OBJECT_ID(‘dbo.MyProc’)) exec(‘CREATE PROCEDURE [dbo].[MyProc] AS BEGIN SET NOCOUNT ON; END’) GO ALTER PROCEDURE [dbo].[MyProc] AS …. Just to avoid dropping the procedure.
You know how sp_MSforeachtable is undocumented, and may go away at any time/be modified? Well, if you’re happy to ignore that, it has another parameter called @whereand, which is appended to the WHERE clause of the internal query that is being used to find the tables (and should start with an AND). You also have … Read more