|
Hello,
I want to pass the sort as an input parameter to the stored proc.
I am constructing a dynamic sql and executing it. The problem is more like a syntax issue, where in I an checking for certain value in sql.
----
Set @sql1 = '
SELECT distinct
sd.storeno storeno,
sd.storename storename
FROM
test_table sd
where sd.status='N'
ORDER by'
set @sql1 = @sql1 + ' ' + @sort
---------
How would I check for additional conditions in this sql? I tried to enclose N in double still did not work.
Many thanks!
|
|
|
|
|
you should use '''N'''
In Word you can only store 2 bytes. That is why I use Writer.
|
|
|
|
|
Thank you for your response.
It should be within two single quotes, like
''N''
|
|
|
|
|
That would be ''''N''''
try:
declare @sql1 varchar(2000)
Set @sql1 = '
SELECT distinct
sd.storeno storeno,
sd.storename storename
FROM
test_table sd
where sd.status=''''N''''
ORDER by'
print @sql1
Output:
SELECT distinct
sd.storeno storeno,
sd.storename storename
FROM
test_table sd
where sd.status=''N''
ORDER by
|
|
|
|
|
When you wish to embed single quotes within dynamic strings, use two consecutive single quotes, like this:
Set @sql1 = '
SELECT distinct
sd.storeno storeno,
sd.storename storename
FROM
test_table sd
where sd.status=''N''
ORDER by'
set @sql1 = @sql1 + ' ' + @sort
EDIT: Corrected a mistake.
modified on Monday, August 1, 2011 2:49 PM
|
|
|
|
|
I don't know but just the double single quotes worked for me.
''N''
|
|
|
|
|
Seems to be some confusion in the back and forth in the answers above.
A SQL text literal is expressed like the following example.
'abc'
If you want to embed a single tick in the above literal you MUST escape it. The standard SQL (ANSI) idiom for that is as follows (but some databases offer other ways.)
'a''bc'
So two single ticks become one.
Three ticks is NEVER correct.
|
|
|
|
|
Priority ways email finding
I have following tables in project
Customer => Customer details
=> Coloums are Customer Name, Customer Type (Gold, Silver, Platinum)
Contact => Can have multiple contacts to a customer
=> Coloums are Customer Id, Name, email, Designation (D1 to D10).
we need to create an SP (MS SQL 2005 ) which returns priority 1 and priority 2 emails of customer (Customer Id will be passed as input) based on following criteria .
When Customer Type is "Gold" Customer, choose email based on following Designation
1. D1, D3,
2 D4, D6
3, D5, D7, D8
Logic :
a). if D1 exist select D1 and move to set 2,
D1 NOT exist select D3 and Move to set 2
D3 NOT exist move to set 2
b) if D4 exist select D4 and move to set 3,
D4 NOT exist select D6 and Move to set 3
D6 NOT exist move to set 3
Note : if we got an email in first step and this step retun two emails. No need to iterate step 3
c) reapeat above logic with step 3 designations sequentially
Continue searching until we get 2 mails.
For "Silver" Customer choose email based on following designation
1. D1, D2,
2 D6, D7
3, D8, D9, D10
For "Platinum" Customer choose email based on following designation
1. D1, D2,
2 D8, D9
3, D3, D4, D10
I am not sure i explained logic very well. Is there any algorith for this type of logic.
|
|
|
|
|
Create a bridge table between CustomerType and EmailDesignation and fill it with data that represents the relationship you specified above. You can then create a query that joins these three tables to get the data that you require.
You also need to create a new table with email designations and their priority.
SELECT TOP 2 C.CustomerName, Co.CustomerType, E.Email, E.Designation
FROM Customer C
INNER JOIN CustomerEmailBridge B ON C.CustomerType = B.CustomerType
INNER JOIN Email E ON B.EmailDesignation = E.EmailDesignation
INNER JOIN EmailPriority P ON E.EmailDesignation = P.EmailDesignation AND B.CustomerType = P.CustomerType
WHERE (C.CustomerID = @CustomerID)
ORDER BY P.Priority
modified on Tuesday, August 2, 2011 2:48 PM
|
|
|
|
|
Shameel,
Simple join will not work here. I need the output based on logic mentioned. It a priority based search.
I am looking for best and optimized way to do it.
|
|
|
|
|
Have a look at my updated answer. Sorry, I missed the priority thing.
|
|
|
|
|
Hi shameel,
this will not work . priority for same designation is different for different customer type
|
|
|
|
|
A little change makes it work. Just add a CustomerType column to the Priority table and include the CustomerType in the <code>WHERE clause.
|
|
|
|
|
Hello,
I connected two computers in LAN 1st having IP 192.168.1.1 and 2nd having IP 192.168.1.2 and i am having sql server management studio express 2005 installed on 1st computer now when i run exe from 192.168.1.2 it throughs an error like.
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.
where as it runs perfetly on 1st computer.
my conection string is
Public DBConn As ADODB.Connection
DBConn.ConnectionString = "Provider=MSDATASHAPE; Data Provider=SQLOLEDB.1;Data Source=TOSHIBA\SQLEXPRESS; Initial Catalog=" & App.Path & "\AVON_AIR_Data.mdf; User ID=sa; Password=; Integrated Security=SSPI; Persist Security Info=False;"
DBConn.Open DBConn.ConnectionString
Please suggest
Thank you.
|
|
|
|
|
|
For remote connections, the Initial Catalog attribute must specify the database name alone, you cannot attach a local database. And the other problem is, you can either specify Integrated Security or User ID/Password, but not both.
|
|
|
|
|
|
Hi Guys,
My data source is FoxPro for Unix using ODBC connection i would like to bring it to sql 2005 by SSIS 2005.
Can any one explain how i could use Dynamic SQL using SSIS Datareader Like for instance
"SELECT * FROM " @[User::TableName] expression???
Please explain the Steps to follow.
Thank you
|
|
|
|
|
Member 8082021 wrote: Can any one explain how i could use Dynamic SQL using SSIS Datareader Like for
instance
Oh this is a fun one; you will need a variable and build the syntax of your query in an expression. Then in the 'data flow', 'source editor' change, 'data access mode' to 'SQL Command from variable. It can be done, just take a little bit of creativity.
Common sense is admitting there is cause and effect and that you can exert some control over what you understand.
|
|
|
|
|
Thank you so much for your help.
I am having an issue could you please give me a more detailed step in solving this problem.
I have to sort this problem now.
Please advice
KB
|
|
|
|
|
I want to see whether the column is created with the option, 'GENERATED ALWAYS AS IDENTITY', to make use of that in my application whether to create the record including the value for that column or not.
Can anyone please help me in getting this attribute/property from any syscolumns information from DB2 database?
|
|
|
|
|
Hi!
I've to Update only the Last record(row) in MYSQL table. How to do this?
|
|
|
|
|
How are you determining what is the "last" record? By Date? ID Number?
|
|
|
|
|
|
I have a table in Sql Server structured something like this:
RowId OrderId OldOrderId Type Example Data
8787 1 NULL I The first order in sequence 1
9837 2 1 U The second order in sequence 1
10838 3 2 U The third order in sequence 1
11873 4 3 U The fourth order in sequence 1
12554 5 NULL I The first order in sequence 2
14113 6 5 U The second order in sequence 2
15003 7 6 U The third order in sequence 2
18343 8 7 U The fourth order in sequence 2
What I am trying to do is find the last record in each sequence using only the columns I have in the table. I cannot add anything to group by as I am not in control of the data. Has anyone got any ideas? I have done something like this before but I cannot seem to put my hands on it at the moment.
Thanks in advance
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
