|
Basic Steps
1. Create/find scheduler
2. Create SQL that gets the data
3. Create code the outputs the data
4. Put 1-3 together.
Steps 1-3 are independent. They do NOT have anything to do with each other.
Step 3 is the only one specific to Excel.
|
|
|
|
|
Hi I have a problem writing an query with select parameter as input paramter in the stored procedure.
CREATE PROCEDURE [dbo].[SelectItemList]
@ColName string,
@ID int
AS
BEGIN TRANSACTION
SELECT @ColName FROM ItemList where ID = @ID
COMMIT TRANSACTION
Is it possible something like this with select paramter also included in input paramter to the stored procedure?
Thanks in advance,
Dhyanga
|
|
|
|
|
Why don't you try along these lines:
CREATE PROCEDURE [dbo].[SelectItemList]
@ColName string,
@FieldName string,
@FieldValue int
AS
BEGIN TRANSACTION
SELECT @ColName FROM ItemList where @FieldName = @FieldValue
COMMIT TRANSACTION
[EDIT: this doesn't solve it. The message by Michael Potter holds the answer.[/EDIT]
modified 29-Jun-12 9:41am.
|
|
|
|
|
Thanks for the reply but it didn't work out. I am restating my problem with following table.
I have one table named ItemList like this:
ID Name Price($)
1 Bag 20
2 Fabrics 35
3 Tools 100
now my query was
CREATE PROCEDURE [dbo].[SelectItemList]
@ColName string,
@ID int
AS
BEGIN TRANSACTION
SELECT @ColName FROM ItemList where ID = @ID
COMMIT TRANSACTION
I need output something like this. If I have @ID = 1 and @ColName = 'Name', then my output should be
Name
-----
Bag
I tried my way that i posted earlier and your way, but it didn't work out. It gave output like this instead.
Column1
--------
Name
Name
Is there any other way or am I doing any wrong in my query?
Please help.
suchita
|
|
|
|
|
You'd need to introduce the output-keyword to one of the parameters, as described in the documentation[^].
Your sproc is a wrapper around a simple statement, adding complexity without any benefits. The design, the approach, it's wrong. There's nothing to "commit" to the database, and a simple select-statement (with parameters) would be sufficient.
Bastard Programmer from Hell
if you can't read my code, try converting it here[^]
|
|
|
|
|
Thanks Eddy, I will take that commit out from my query. I mistakenly forgot to take that out. But my question is still the same. I don't know whelther we can send fieldname as input parameter to get the value of that field.I know it looks very weird for that small table but I have to use the same concept for my huge database system. I thought that table would be easy for me to explain what my output should look like. I am going through the documentation you sent me but at glance, i couldn't see what I am looking for but I am going through it line by line. Thanks for your time .
suchita
|
|
|
|
|
Dhyanga wrote: I don't know whelther we can send fieldname as input parameter to get the value of that field.
You could join on the system-tables, but to what use? You'll be concatenating constants in a way that can hardly be considered helpfull. If writing Sql is that much of a problem, consider a ORM-framework.
Bastard Programmer from Hell
if you can't read my code, try converting it here[^]
|
|
|
|
|
I had got the required output simply writing the sql query but I wanted it on stored procedure. I know i can do it something like this in my query.
string colname;
int ID;
string query;
.
.
.
query = "select " + colname + " from ItemList where ID = " + ID ;
SqlCommand cmd = new SqlCommand(query, sqlconn);
.
.
.
.
This had solved my problem but I wanted it on stored procedure. and I was stuck giving the column name itself as input parameter.
suchita
|
|
|
|
|
I can only point to jschells' comment
Bastard Programmer from Hell
if you can't read my code, try converting it here[^]
|
|
|
|
|
Thanks Eddy for your time and views.
suchita
|
|
|
|
|
Dhyanga wrote: I don't know whelther we can send fieldname as input parameter
You can't.
Your choices are
1. Create the SQL dynamically in C# and then execute it.
2. Create the SQL dynamically in SQL and then execute it in SQL.
3. Use a fixed set of fixed SQL statements and select one based on the data passed in.
|
|
|
|
|
Thanks Jschell.. Just curious if that can be possible. If not, i can just execute it without using stored procedure. I was trying to use better way than that if I could...
suchita
|
|
|
|
|
Dhyanga wrote: i can just execute it without using stored procedure
My solutions work with or without stored procs.
|
|
|
|
|
Hi,
try like this..
CREATE PROCEDURE [dbo].[SelectItemList]
@ColName string,
@ID int
AS
BEGIN TRANSACTION
SELECT @ColName = ColumnName FROM ItemList where ID = @ID
COMMIT TRANSACTION
Karthik Harve
|
|
|
|
|
Am I missing something, you need dynamic SQL to meet this requirement, you solution does not work!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
You have to use sp_executesql and construct the query string yourself. Do realize that you can open yourself up to SQL injection attacks if you don't properly test the variables.
CREATE PROCEDURE SelectItemList
(
@ColName string,
@ID int
)
AS
DECLARE @sql NVARCHAR(4000)
SET @sql = N'SELECT ' + @ColName + N' FROM ItemList where ID = ' + CAST(@ID AS VARCHAR(10))
EXEC sp_executesql @sql
|
|
|
|
|
Thank you Micheal. That's what I wanted but we need to give the @ColName nvarchar size too. It worked fantastic.
suchita
|
|
|
|
|
|
Keep in mind that as formatted that solution is open to SQL injection attacks.
|
|
|
|
|
Hi All,
How we make the .mdf file of a database ?
I want a script or C# code that could be used in a utility in C#.Net.
Please help !!
Regards,
Lalit
modified 27-Jun-12 4:58am.
|
|
|
|
|
lalitN wrote:
How we make the .mdf file of a database ?
That extension is used both for Sql Server as well as Microsoft Access. Which are you targetting?
Bastard Programmer from Hell
|
|
|
|
|
I am using SQL Server 2008 R2.
|
|
|
|
|
The command to create a database is documented here[^], examples are at the bottom of the page.
Bastard Programmer from Hell
|
|
|
|
|
Hi,
Can we create the mdf file by taking the backup of one existing database ?
Regrds,
Lalit
|
|
|
|
|
Yes. Backup the existing database, and restore [^] it. Keep in mind that this procedure will convert "older" database versions (backups from 2005) to the format of your server (2008R2).
Bastard Programmer from Hell
|
|
|
|