The confusion is justified to a degree – and other RDBMS like Oracle do have stored procedure parameters which can be of type IN (input only), OUT (output only), or INOUT (both ways – “pass by reference” type of parameter). SQL Server is a bit sloppy here since it labels the parameter as OUTPUT, but … Read more
maybe i’m too old-school, or too lazy, or both, but i have to disagree. Time and again stored procedures have ‘saved the day’ because when a minor back-end change or bug appears we only have to fix the stored procedure instead of updating the desktop application on several dozen desktops plus the web server. In … Read more
By following the naming conventions , you will find it’s called : ADO.NET Entity Framework , which means that Entity Framework sits on top of ADO.NET so it can’t be faster , It may perform both in equal time , but let’s look at EF provides : You will no more get stuck with writing … Read more
1.One way is: a) Declare your variables b) Assign values to them with a single select statement c) Execute the procedure passing the local variables d) Execute the following in a loop using WHILE or CURSOR in order to apply this for all rows in TABLE1 DECLARE @param1 <DATATYPE>, @param2 <DATATYPE>, … SELECT TOP 1 … Read more
The @ makes it a user defined session variable. Otherwise it would be locally scoped variable (in a stored procedure), you would have to DEFINE your local before you can SET it. You could also set a global system variable (with SET GLOBAL or SET @@global) if you wanted to. As well as a session … Read more
The supplied value must be a constant or a variable. You cannot specify a function name as a parameter value. (from MSDN Executing Stored Procedures). You need to do something like this: declare @EffectiveStartDateText varchar(30) set @EffectiveStartDateText = cast(@EffectiveStartDate as varchar) declare @EffectiveEndDateText varchar(30) set @EffectiveEndDateText = cast(@EffectiveEndDate as varchar) RAISERROR ( ‘SPName: InsertUpdateLiquidityDateRule: Start … Read more
You can create a temp table and then use INSERT INTO #MyTable EXEC procedure @param. There are some other techniques listed here.
If you want to do this with a prepared statement, then you need to include the variable assignment in the original statement declaration. If you want to use a stored routine it’s easier. You can assign the return value of a stored function directly to a variable, and stored procedures support out parameters. Examples: Prepared … Read more
Concepts such as “loops” (for-each, while, etc) and “branching” (if-else, call, etc) are procedural and do not exist in declarative languages like SQL. Usually one can express one’s desired result in a declarative way, which would be the correct way to solve this problem. For example, if the testProc procedure that is to be called … Read more
Since PostgreSQL 8.4 (which you seem to be running), there are default values for function parameters. If you put your parameter last and provide a default, you can simply omit it from the call: CREATE OR REPLACE FUNCTION foofunc(_param1 integer , _param2 date , _ids int[] DEFAULT ‘{}’) RETURNS SETOF foobar — declare return type! … Read more