|
<quote>
"When inserting or updating via proc that it is a good practice to return the new or updated rec using output parameters."
Opinions - one way or the other - appreciated.
Thanks
|
|
|
|
|
It really depends on how it is being used, and I personally use both techniques. I can't say I've read much on the benefits/pitfalls of each approach, but within T-SQL, I prefer the OUTPUT param simply to favor variable assignment rather than having a result set returned. If the stored procedure is being called from .NET, I usually SELECT out the record ID, and utilize the ExecuteScalar method of SqlCommand.
The T-SQL usage doesn't really favor one over the other (in terms of keystrokes):
EXECUTE StoredProc @Param1, @Param2, @RecordID OUTPUT
SET @RecordID = EXECUTE StoredProc @Param1, @Param2
OUTPUT Parameter:
If stored procedure is being called from within T-SQL.
If the new record ID is not always used by the caller.
SELECT Record ID:
If stored procedure is being called from .NET (use SqlCommmand.ExecuteScalar).
If the new record ID is always used by the caller.
If you do choose to take the OUTPUT parameter route, I would suggest you are careful about how you assign the new record ID to that OUTPUT parameter. If the stored proc is being called within a loop, you'll want to make sure the previous value does not remain in the output parameter (in the event that a record is not actually affected). You may also want to set the default value for that OUTPUT param as NULL so it is optional (in the event the caller does not require the record ID).
|
|
|
|
|
Member 4501940 wrote: that it is a good practice to return the new or updated rec using output parameters.
Having multiple paths to Rome doesn't mean that one traderoute is the "bestest". Some paths are optimized for heavy traffic, while others are only accessible by horse.
It's a good practice to let the programmer decide what solution would fit best into the problem at hand. It might be quite acceptable to return the identity using a simple select statement 
I are Troll 
|
|
|
|
|
Hi,
I am using MS SQL Server 2008 Express.
I have a product table with columns Id and Name. I changed these columns to ProductId and ProductName. In my stored procedure it's not finding ProductId and ProductName, but rather Id and Name. And now it underlines ProductId and ProductName in red. How do I update it so that it sees these columns?
Regards,
Brendan
|
|
|
|
|
Try this,
Right Click the Tables branch in the Server Explorer.
Click Refresh. Open the branch for the changed tables - have the changes taken?
On the Edit Menu - Click on Intellisense/Refresh Local Cache.
Does this help?
I don't speak Idiot - please talk slowly and clearly
'This space for rent'
Driven to the arms of Heineken by the wife
|
|
|
|
|
Andy_L_J wrote:
Right Click the Tables branch in the Server Explorer. Click Refresh.
Nope this does not work.
Andy_L_J wrote:
Open the branch for the changed tables - have the changes taken?
Where do I find this?
Andy_L_J wrote:
On the Edit Menu - Click on Intellisense/Refresh Local Cache.
There is no intellisense option from which to choose.
From where are you directing me, from SQL Server Management Studio 2008?
|
|
|
|
|
Did Ashcroft' answer help? You will have to change all references to these field names in all Functions and Stored Procedues that call them in code.
I don't speak Idiot - please talk slowly and clearly
'This space for rent'
Driven to the arms of Heineken by the wife
|
|
|
|
|
You need to recompile your stored procedure. It will then reflect the new column names.
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
Hi,
Why can't I name a column with the name Name? When I say "SELECT Name..." then Name appears in blue. Why is this? Then I use [Name]. Are then any such similar words that I need appear in blue that I need to be aware of?
I have a product table, and the name of the product I called Name. Is this incorrect? Should I rather call it ProductName?
Regards,
Brendan
|
|
|
|
|
.NET Enthusiast wrote: Name appears in blue. Why is this?
Those are keywords[^].
.NET Enthusiast wrote: Then I use [Name]
You can even name a column "select column", provided that you enclose it with those brackets. That would give you something like this;
SELECT [select column] FROM [tablename]
.NET Enthusiast wrote: I have a product table, and the name of the product I called Name. Is this incorrect? Should I rather call it ProductName?
Both are allowed as column-names. Does a product have name, or a productname?
I are Troll
|
|
|
|
|
Check out the keyword list in Eddies post and NEVER use them. Even square bracketing them should never be allowed IMHO.
Spaces in column names another horrible option that should NEVER be used. If you need to use a square bracket then there is something wrong.
IIRC there used to be an app/database (I think it may have been an old ODBC driver) that refused to support [] and therefore any column requiring a [] could not be used.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thanks for your reply, but I don't see Name anywhere in that list. Description is also blue so I am assuming it is a keyword as well, but I don't see it either in that list.
BTW, what does IMHO and IIRC stand for?
|
|
|
|
|
Not sure of the reason why name and description are highlighted blue but I only apply the rule to [], highlights do not affect the use of the word.
.NET Enthusiast wrote:
BTW, what does IMHO and IIRC stand for?
Are you serious, 7 years you have been a member and not run across these contractions, this site may help[^]
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
It blue probably because they are keywords, but why isn't Name on the keywords list, if they are not there then how do I know that it is a keyword?
|
|
|
|
|
.NET Enthusiast wrote: but why isn't Name on the keywords list
Because that particular example, name isn't a standalone keyword, but rather a modifier for an existing keyword. It's used with the create database[^] command;
CREATE DATABASE database_name
[ ON [PRIMARY] [ <filespec> [,...n] ]
[, <filegroup> [,...n] ]
]
[ LOG ON { <filespec> [,...n] ]
[ FOR LOAD | FOR ATTACH ]
<filespec> ::=
( [ NAME = logical_file_name, ]
FILENAME = 'os_file_name'
(and more)
SQL Server is also using these keywords as column-names, even in the master tables;
select *
from sysobjects
where type = 'U'
order by name
Yes, it will highlight the "name" keyword, but that doesn't mean that it's an erroneous statement.
I are Troll
|
|
|
|
|
|
Hello everybody...
Anybody who can share how to decrypt a stored procedure of MSSQL 7?
xxx
|
|
|
|
|
Hi,
@Text ='%hema%'
@itemcount int
SET @SQL ='SELECT COUNT(*) FROM dbo.table
WHERE (LOWER(column) LIKE LOWER('+@Text+') OR LOWER(column) LIKE LOWER('+@Text+'))
exec @SQL
i want out put of that select (i.e count(*)) in the local variable
@itemcount.
if i give like
set @itemcount = EXEC @SQL
i m getting syntax error, how to take that?
|
|
|
|
|
Example:
declare @itemcount int
set @itemcount = (select isnull(count(*),0) from tablename where columnname=conditionvalue)
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
www.aktualiteti.com
|
|
|
|
|
thanks for your response blue.
My question is executing the query dynamically
that is using
EXEC 'query'
i need this output in a variable.
i hope you can get me or read my prev message fully
|
|
|
|
|
You may want to take a look at the sp_executesql system stored procedure: http://msdn.microsoft.com/en-us/library/ms188001.aspx[^]
This allows you execute the dynamic SQL string, as well as specify parameter definitions and parameter assignments that can bind local SQL variables to the dynamic SQL call.
DECLARE @InputParam int,
@OutputParam int
EXECUTE sp_executesql 'query',
N'@DynamicInputParam int, @DynamicOutputParam int OUTPUT',
@DynamicInputParam = @InputParam,
@DynamicOutputParam = @OutputParam OUTPUT
Within 'query', use the dynamic parameters/variables defined in the 2nd parameter (@DynamicInputParam and @DynamicOutputParam). Within 'query', these variables will initially contain the values of (@InputParam and @OutputParam). If a dynamic parameter is defined as an OUTPUT parameter (@DynamicOutputParam), any assignment to that variable will be reflected in the local variable (@OutputParam) after the call to sp_executesql.
|
|
|
|
|
hi peoples,
select * from table where lower(column) like lower('%test%')
this statement works fine for me, but
declare @abc char(50)
set @abc='%test%'
select * from table where lower(column) like lower(@abc)
its not working for me?
should i go for dynamic query though??
can any one help me?
thanks in advance and happy new year
|
|
|
|
|
Hema Bairavan wrote: its not working for me?
What error you are getting ?
Abhijit Jana | Codeproject MVP
Web Site : abhijitjana.net
Don't forget to click "Good Answer" on the post(s) that helped you.
|
|
|
|
|
abhijit,
i am not getting any error,
but the result s not correct, its not filtering the correct data from the database.and sorry for late reply.
|
|
|
|
|
Try this (I haven't tested)
DECLARE @ABC VARCHAR(50)
DECLARE @SQL VARCHAR(1000)
SET @ABC = '%test%'
SET @SQL = 'SELECT * FROM table WHERE lower(column) LIKE (''' + @ABC + ''')'
EXEC (@SQL)
I don't speak Idiot - please talk slowly and clearly
'This space for rent'
Driven to the arms of Heineken by the wife
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
