|
.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
|
|
|
|
|
thanks for response andy,
regarding dynamic query i can do,
but it will affect the perfomance, because it has to compile when ever you are running the select statement(as i m using the same in procedure)
my question is y cant a static one?
|
|
|
|
|
Haven't tried this, but noticed you are using the type char(50) instead of varchar(50). Is the char(50) adding padded characters to the end of the string, causing it to not match?
|
|
|
|
|
yes darren, thanks for your info.
me to not noticed the same. its working fine when i execute from the procedure from the SQL Query editor.
but if i run from the application (asp.net C#) using command.executereader(),
i m not getting the result, do you have any ideea?
|
|
|
|
|
Hi,
i want to insert some users in a table. before inserting those users i want to check that user is already exists or not. so i want to written one sp.can any one please help me by posting some sample Storedproc
fttyhtrhyfytrytrysetyetytesystryrty
|
|
|
|
|
Here it is!
create procedure InsertUser
@username varchar(200)
as
Begin
if(select count(*) from users where username=@username) = 0)
begin
insert into users values(column1,column2......)
end
end
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
|
|
|
|
|
Hi ,
Thanks for reply
in this stored proc i want to return some value. so that i can handle in my C# sharp code.
i this case i want else condition and some return value
fttyhtrhyfytrytrysetyetytesystryrty
|
|
|
|
|
Then I will suggest you to use function
create function InsertUser
@username varchar(200)
as
Begin
return 0 --By default user is not inserted and return value have value 0
if(select count(*) from users where username=@username) = 0)
begin
insert into users values(column1,column2......)
return 1 --User is inserted and return value have value 1
end
end
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
|
|
|
|
|
Try this
CREATE PROCEDURE [dbo].[USP_CheckUserAvailability]
-- Add the parameters for the stored procedure here
(
@UserName AS VARCHAR(50)
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
IF EXISTS( SELECT [User Name] FROM tbl_User WHERE [User Name] = @UserName )
BEGIN
SELECT * FROM tbl_User
END
ELSE
INSERT INTO tbl_User ([User Name]) VALUES (@UserName)
END
Hope this helps
Niladri Biswas
|
|
|
|
|
Not answers really, just pointers that people may know of to any industry and business segment specific field name standards documents out there.
Things that address naming conventions across various industry\business segments so that I can look to normalize a set of tables for a generalized system that could cross industry boundaries and still manage to keep some standardization across industry expectations would be great.
I admit here I have just start my search so perhaps I am being a bit lazy But is IS 3:53 AM on Jan 1 and I am up thinking about THIS so I figured I'd ask the masses here for some advice at the start of the new year..
Thanks for any pointers.
|
|
|
|