A cursor will only accept a select statement, so if the SQL really needs to be dynamic make the declare cursor part of the statement you are executing. For the below to work your server will have to be using global cursors. Declare @UserID varchar(100) declare @sqlstatement nvarchar(4000) –move declare cursor into sql to be … Read more
If you have one column that is an IDENTITY, just do this INSERT MyTable DEFAULT VALUES; –allows no column list. The default will be the IDENTITY SELECT SCOPE_IDENTITY(); If you don’t have identity, then can you set it? This is the best way.. and use the SQL above. If not, you want to insert a … Read more
You can declare it like this: CREATE PROCEDURE MyProcName @Parameter1 INT = 1, @Parameter2 VARCHAR (100) = ‘StringValue’, @Parameter3 VARCHAR (100) = NULL AS /* Check for the NULL / default value (indicating nothing was passed) */ if (@Parameter3 IS NULL) BEGIN /* Whatever code you desire for a missing parameter */ INSERT INTO …….. … Read more
Have a look at this example – CREATE TABLE table1( column1 VARCHAR(255) DEFAULT NULL, column2 VARCHAR(255) DEFAULT NULL, column3 VARCHAR(255) DEFAULT NULL ); INSERT INTO table1 VALUES (‘1’, ‘value1’, ‘value2’), (‘2’, ‘value3’, ‘value4’); DELIMITER $$ CREATE PROCEDURE procedure1(IN Param1 VARCHAR(255), OUT Param2 VARCHAR(255), OUT Param3 VARCHAR(255)) BEGIN SET @c2 = ”; SET @c3 = ”; … Read more
Another option would be as the return value for the stored procedure (I don’t suggest this though, as that’s usually best for error values). I’ve included it as both when it’s inserting a single row in cases where the stored procedure was being consumed by both other SQL procedures and a front-end which couldn’t work … Read more
CHARINDEX() searches for a substring within a larger string, and returns the position of the match, or 0 if no match is found if CHARINDEX(‘ME’,@mainString) > 0 begin –do something end Edit or from daniels answer, if you’re wanting to find a word (and not subcomponents of words), your CHARINDEX call would look like: CHARINDEX(‘ … Read more
CREATE PROCEDURE [MySPROC] AS BEGIN –supplying a data contract IF 1 = 2 BEGIN SELECT cast(null as bigint) as MyPrimaryKey, cast(null as int) as OtherColumn WHERE 1 = 2 END CREATE TABLE #tempSubset( [MyPrimaryKey] [bigint] NOT NULL, [OtherColumn] [int] NOT NULL) INSERT INTO #tempSubset (MyPrimaryKey, OtherColumn) SELECT SomePrimaryKey, SomeColumn FROM SomeHugeTable WHERE LimitingCondition = true … Read more
You can use mysql scheduler to run it each 5 seconds. You can find samples at http://dev.mysql.com/doc/refman/5.1/en/create-event.html Never used it but I hope this would work: CREATE EVENT myevent ON SCHEDULE EVERY 5 SECOND DO CALL delete_rows_links();
You should be storing the codes in a separate table, since you have a many to many relationship. If you separate them, then you will easily be able to check. It would be possible to do in the type of system you have now, but would require text searching of the columns, with multiple searches … Read more
It means that no rows will be returned if @region is NULL, when used in your first example, even if there are rows in the table where Region is NULL. When ANSI_NULLS is on (which you should always set on anyway, since the option to not have it on is going to be removed in … Read more