HELP in "Where" clause

Hi,
I would like to pass a stream of varchar as my "where" clause as follow.
Select * from test where + @.Filters
I can't get this work until I declare the SQL as varchar and then execute th
e query as follow:
DECLARE @.SQL VARCHAR(2000)
SET @.SQL = 'Select * from test where ' + @.Filters
EXEC (@.SQL)
Is there a way that I can implement the SP in my first example?Hi,
You can do like this;
declare @.filters varchar(30)
set @.filters='1 =1'
exec('Select * from test where '+ @.Filters)
You can change the where clause based on ur requirement.
Thanks
Hari
MCDBA
"Abi" <anonymous@.discussions.microsoft.com> wrote in message
news:4E5BACA6-9C0B-4643-A3DD-C65375830D37@.microsoft.com...
> Hi,
> I would like to pass a stream of varchar as my "where" clause as follow.
> Select * from test where + @.Filters
> I can't get this work until I declare the SQL as varchar and then execute
the query as follow:
> DECLARE @.SQL VARCHAR(2000)
> SET @.SQL = 'Select * from test where ' + @.Filters
> EXEC (@.SQL)
> Is there a way that I can implement the SP in my first example?|||No, you need dynamic SQL for that. But please consider below first:
http://www.sommarskog.se/dynamic_sql.html
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Abi" <anonymous@.discussions.microsoft.com> wrote in message
news:4E5BACA6-9C0B-4643-A3DD-C65375830D37@.microsoft.com...
> Hi,
> I would like to pass a stream of varchar as my "where" clause as follow.
> Select * from test where + @.Filters
> I can't get this work until I declare the SQL as varchar and then execute
the query as follow:
> DECLARE @.SQL VARCHAR(2000)
> SET @.SQL = 'Select * from test where ' + @.Filters
> EXEC (@.SQL)
> Is there a way that I can implement the SP in my first example?

HELP HELP varchar

I have a field type of "varchar"

It can not accept to Store Arabic Characters!!! plus i can't change it into ntext or nvarchar my system which goin to export the data into SQL server by suing insert command will hang. so im stucked with varchar!!!

here some sample of my data stored currently in My SQL SERVER 2000

" /   "

Plzzzz help me if there any other way to do it

Do you have the right collation set in your database?

Have a look in BOL (Books on Line) at COLLATE - there's a lot of info in there about how to deal with charachters from different languages and alphabets.

/Kenneth

|||If you want to store arabic characters you will have to change your column data types to nvarchar. So you will have to investigate the problem you are describing while using the nvarchar column. What is the actual problem here ? I am not sure / doubt that this is related to collation problems, but further investigation might help us to solve the problem.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.desql

help for creating stored procedure

ALTER PROCEDURE companyinsert

@.companyid INT IDENTITY(1,1) NOT NULL,
@.companyname varchar(20),
@.address1 varchar(30)

AS

INSERT INTO companymaster
( companyname, address1)
VALUES (@.companyname,@.address1)

i don't want the companyname having the same names are recorded again with the different company id..

Can anyone help me and modify my code according it's giving error...in the @.companyid.

It is being done in sql server 2005 with asp.net C# 2005

You cannot declare a parameter with IDENTITY property in a stored proc. I think what you are looking for is more along these lines. You might want to read up documentation on SCOPE_IDENTITY(). Briefly, it returns the Identity value that was created due to your INSERT.

ALTER PROCEDURE companyinsert@.companyidINT OUTPUT,@.companynamevarchar(20),@.address1varchar(30)ASBEGINSET NOCOUNT ONIFNOT EXISTS(SELECT *FROM companymasterWHERE CompanyName = @.companynameAND Address1 = address1)BEGININSERT INTO companymaster ( companyname, address1)VALUES (@.companyname,@.address1)SELECT @.companyid = SCOPE_IDENTITY()ENDSET NOCOUNT OFFEND

help coverting a varchar to a float

Hi,

I'm using the following code to convert a varchar to a float in a trigger.

declare @.acre varchar (6)

set @.acre_size = 0.0

select @.acre = plotsizeacre
from inserted

declare @.num int
select @.num = isnumeric (@.acre)

if @.num = 1
set @.acre_size = @.acre

This normally works fine, but I'm getting errors if the plotsizeacre field is 1,75

Casting to a float or converting to a float also gives errors.

Any ideas how to solve this problem? (The field would normally be filled in properly, eg 1.75).

Thanks in advance,

Ian

You cannot use ISNUMERIC to do strick checking. This function will return 1 for value that can be converted to any of the integer, numeric, float and money data types. The value '1,75' can be converted to money but not float. Your best option is to chnage the schema and modify the column to float. This will require modifications from the client side also to make sure that the value that user enters is typed accordingly. If you have to keep the varchar data type then you will have to perform the cleaning of the value yourself - meaning you have to check for bad formats and convert appropriately or error out gracefully.

Help - To use Create #table in Exec command

Hi,
I need the use Create table #table command in the Exec command
Example
declare @.chr_CreateSql nVarchar(4000)
,@.err Varchar(255)
select @.chr_CreateSql = ' Create table #test (ss varchar(255))'
Exec (@.chr_CreateSql)
select * from #test
When I run this I get the following error
But when I change #test to ##test
It works fine.
The reason I need the create table in exec command the number of columns in
temp table is dynamic. Please help me to resolve this issue
Thanks
Senthil> select @.chr_CreateSql = ' Create table #test (ss varchar(255))'
> Exec (@.chr_CreateSql)
> select * from #test
Every day, it seems.
EXEC() has its own scope. When EXEC() has finished, your #temp table no
longer exists.
You can fool it by doing this all in one EXEC() call:
EXEC('CREATE TABLE #test(ss VARCHAR(255)); SELECT * FROM #test')
Or, figure out a way to drop the temp table, or the dynamic SQL, or both.
Neither are considered "best practices"... dynamic SQL is treated quite well
here: http://www.sommarskog.se/dynamic_sql.html

> But when I change #test to ##test
This is a global temp table. I recommend you do not use this syntax unless
you know the pros and cons.

> The reason I need the create table in exec command the number of columns
> in
> temp table is dynamic.
Why not use all the columns and then ignore the ones you don't need.
A

help

what is the exact differnece char and varchar in inputwise means(which
one take alphabets and numerics)?
*** Sent via Developersdex http://www.examnotes.net ***Allowed data is the same for both, the difference between them are the
length of the input.
Examples:
DepartmentId dataset
Aabbaa, aabbbb, aabbcc, aabbdd, aabbee.
Expected data is always 6 characters therefore use char - eg char(6).
FirstName dataset
Andrew, Melody Suk Man, Allan, Cathy, Arthur, Shirly, Anthony, Wendy
Expected data can be any length in character therefore just put an
upperbound on it and use varchar - eg. Varchar(30)
Mel