|
EXEC (str)
or
sp_executesql str
But both are awful ways to implement a search query - and im betting you're passing textbox values directly into the SP - opening you up to sql injection attacks.
You should be doing it like the following, passing null for any params where you dont have a value (or not passing them at all, and taking the default, null)
CREATE PROCEDURE sp_srch(
@empid varchar(10) = NULL,
@empname varchar(45) = NULL,
@mobileno varchar(15) = NULL,
@bloodgroup varchar(5)) = NULL
BEGIN
select Emp_Id,First_Name,Last_Name,Gender,Mobile_No,Email_id,Blood_Group
from Employee
where (@empID IS NULL OR emp_id like '%' + @empID + '%')
and (@empName IS NULL OR first_name like '%' + @empName + '%')
and (@mobileno IS NULL OR mobile_no like '%' + @mobileno + '%')
and (@bloodgroup IS NULL OR blood_group like '%' + @bloodgroup+ '%')
|
|
|
|
|
J4amieC wrote: You should be doing it like the following, passing null for any params where you dont have a value (or not passing them at all, and taking the default, null)
Or passing 0 if is can be assumes that null and 0 are equivalent, this is then more fault tolerant
(ISNULL(@empID, 0) = 0 OR emp_id like '%' + @empID + '%')
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Mycroft Holmes wrote: Or passing 0 if is can be assumes that null and 0 are equivalent, this is then more fault tolerant
All the parameters were varchar, so passing zero is impossible.
However your point is a valid one - for numeric optional input I would most certainly have the default as zero and check for nulls in the way you suggested.
|
|
|
|
|
J4amieC wrote: ll the parameters were varchar
Did not even look at that  I assumed anything with an ID is numeric and this structure is almost rote for me.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Little help with returning both the name and the current identity for all tables? If a table doesn't have any rows, instead of the current identity, I'd like to return 0. The database will be in single user mode during the query if that helps.
The following query is close but it still returns 1 for tables that don't have any rows. Somehow the CASE expression isn't right.
Thanks in advance.
SELECT
TABLE_NAME,
CASE
WHEN COUNT(TABLE_NAME) = 0 THEN
0
ELSE
IDENT_CURRENT(TABLE_NAME)
END
FROM
INFORMATION_SCHEMA.TABLES
WHERE
OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), 'TableHasIdentity') = 1
AND
TABLE_TYPE = 'BASE TABLE'
GROUP BY
TABLE_NAME
|
|
|
|
|
SELECT CASE <br />
WHEN COUNT(TABLE_NAME) = 0 THEN 0<br />
WHEN COUNT(TABLE_NAME) > 0 THEN IDENT_CURRENT(TABLE_NAME)<br />
END<br />
FROM INFORMATION_SCHEMA.TABLES<br />
WHERE TABLE_TYPE = 'BASE TABLE'<br />
GROUP BY<br />
TABLE_NAME
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
|
|
|
|
|
Maybe I'm missing something here. How can that ever return 0?
If I have understood it correctly, this is what the query will do:
Select the rows from TABLES with type "BASE TABLE"
Group them by TABLE_NAME
Counts the number of rows for each TABLE_NAME
If the count is 0, return 0
If the count is not 0, return the identity of the table
But, surely the only way COUNT(TABLE_NAME) can be 0 is if there are no rows in TABLES with this TABLE_NAME and type "BASE TABLE". In which case, this query would never pick up that table name, so it would never appear in the result set. So for any TABLE_NAME that this query picks up, the count will always be greater than 0.
|
|
|
|
|
Dear all
I have aproblem with my websit veiwing arabic data, when I importing data from csv file into web host sqlserver database the data in tables appears like ??????? and squares also in web pages, for notes the arabic field data type is nvarchar max and collation is arabic language .
so If any body have the answer please help me fast
Thank you
Lamis
|
|
|
|
|
But how are you importing the data. What encoding are you using when you read the data in? Just because the database is storing these values doesn't mean that the problem is at the database end - it's entirely possible that the problem is at the data population end.
|
|
|
|
|
Hi, am designing a database that expedite collection of data from the insurance companies by insurance body. members are required to upload daily transaction in xml format.the insurance policy no is the primary key but other information can change. the data will be query by users. should i store the data as xml datatype or in split the column as use cdc to audit the canges or plit the column with history table . like below
Field Name Data Type
Policy No Var
Office_Code Var
Vehicle_Reg_Number Var
Vehicle_Chas_Number var
Vehicle_Eng_Number Var
Date_Policy_Start_Date Date
Date_Policy_End_Date Date
Date_of_Cancellation Date
or
policyno var
policydata xml
|
|
|
|
|
Do NOT store your data using the XML data type, do the job properly and split the info into columns. The benifits are innumerable but include indexing and search/query ease of use. I hate XML.
Storing the history information is a business requirement, not a technical question.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
but dont you think i will change the column any time the xml schema change. that mean flexibility challange
|
|
|
|
|
HI,
i want to filter no.of rows where field1 contains symbol like %. How to select only those records contains like 20%,30%...etc.
************ S G KORE *******************
|
|
|
|
|
SELECT * FROM [Table] WHERE [Field] LIKE '%[%]'
|
|
|
|
|
|
eddieangel wrote: There will always be a 1:1 relationship between the two tables.
If all the attributes depend on the key[^], then they should be in the same table. Put an index on the fields that you're going to search often 
I are Troll 
|
|
|
|
|
I'd also add to the above comment to create a RealProperty view that queries on only those columns that deal with RealProperty and like wise for the HomeOwner data as well. Just make sure that each view uses the index fields defined at the table level and everything should work fine.
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
|
|
|
|
|
|
I do believe your data structure is incorrect. Your constraint of 1:1 between owner and property fails when someone own more that 1 property or a property has more than 1 owner or a property is sold (I assume you wish to retain history of the property).
As Piebald suggested a many:many link table is what you need to implement.
What happens if you have a company own the property, now you are in the position of needing an address table so you need a many:many link between owner and address...
Designing a data structure is not a trivial excercise and needs time and effort to get it right. It is guaranteed that you will not get it right the first time. Be prepared to admit an error and redesign ASAP, allowing an incorrect design to persist only deepens the cost of fixing the problem.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I appreciate the insight. Thank you.
|
|
|
|
|
I agree with the statement that I think your reasoning is flawed.
More often then not you are going to see multiple owners to a home (or any other entity) simply because of things like marriage, partnerships, trusts, etc... Also, keeping history now requires that you set up 2 tables that contain the same data linked to a building. Keeping it all in one table and adding an attribute to show current vs. past ownership I think is the better option.
In fact, you are probably really going to want to separate out the table that tracks the data about the building, the tables that track the address data of all the possible owners and then use a join table to crate the linking between the two. That I think is the best way to achieve a clean design. When you consider what else you can store in this third reference table (IE: the data about the sales agent(s) involved, the inspectors used durring the sales, etc...) you start to see how it works cleaner.
|
|
|
|
|
Hi,
For an example, using Sql Server - let's say I have three tables:
Client:
ID
Name
File:
ID
Name
Bytes
ClientID
Data:
ID
DataField
FileID
Each record in Data can be linked back to each Client record through the File Record. I want the DataField value to be unique only per client - so two values of "42" in the DataField is ok as long as they link to different clients.
I could put the ClientID in the Data table, but that would then mean I would need to be sure the ClientID in the related File record always matches.
Can an index be set on the Data table using the DataField field and the Client ID field?
If so, how would you do this? If not, is there an alternative?
Thanks
|
|
|
|
|
Stryder_1 wrote: I want the DataField value to be unique only per client
How about this?
Client
ID
Name
DataField
File
ID
Name
Bytes
ClientID
That's what you asked literally, but not what you'd want;
Client
ID
Name
File
ID
Name
Bytes
DataField
ClientID
With a UNIQUE constraint on DataField and ClientId
I are Troll
modified on Monday, October 18, 2010 3:56 PM
|
|
|
|
|
I think you can add two unique constaint in File..ClientID and Data..FileID to achieve your goal. By your data design, if you want the DataField value to be unique only per client, the infomation in [File] should also unique match one [Client] record.
Client:
ID
Name
File:
ID
Name
Bytes
ClientID <<<
Data:
ID
DataField
FileID <<<
|
|
|
|
|
We have a linked server on SQL 2005 to database on AS400. the linked server is made using Microsoft DB2OLEDB provider.
when we are using the linked server, we are getting the following error:
OLE DB provider "DB2OLEDB" for linked server "AS400PROD" returned message "".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "DB2OLEDB" for linked server "AS400PROD".
When we restart the SQL server machine, the linked server works fine for about 5 minutes then we receive the same error as described before,
The script of the linked server is :
EXEC master.dbo.sp_addlinkedserver @server = N'AS400PROD', @srvproduct=N'AS400', @provider=N'DB2OLEDB', @datasrc=N'S6595D7F', @provstr=N'Provider=DB2OLEDB;Password=ISA108;Persist Security Info=True;User ID=ASI;Initial Catalog=S6595D7F;Data Source=AS400;Network Address=192.1.1.50;Package Collection=KFILFNB;DBMS Platform=DB2/AS400;Process Binary as Character=True'
/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'AS400PROD',@useself=N'False',@locallogin=NULL,@rmtuser=N'LOGINRPORT',@rmtpassword='########'
GO
EXEC master.dbo.sp_serveroption @server=N'AS400PROD', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'AS400PROD', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'AS400PROD', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'AS400PROD', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'AS400PROD', @optname=N'rpc', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'AS400PROD', @optname=N'rpc out', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'AS400PROD', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'AS400PROD', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'AS400PROD', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'AS400PROD', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'AS400PROD', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'AS400PROD', @optname=N'use remote collation', @optvalue=N'true'
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
.
.