I am feeding in @RsCategory varchar (25) into my stored procedure. I was hoping to make the RsCategory = the column name from the table I would like to select data from.
How can I use @RscCategory as the column name value in the select statement? (or can I?)
select @RsCategory as displaydata
From tTable T
where T.MyOtherParams=@MyOtherParams
Any help would be greatly appreciated!
Greetings, I am developing a search form that uses a DDL to select a column name and a text box to define a search term. Code follows: 1 cmdSearch = New SqlCommand( "select * from sites where @columnName like @sTerm order by site_name", conSites)
2
3 cmdSearch.Parameters.Add( "@columnName", SqlDbType.Text).Value=ColumnParam
4 cmdSearch.Parameters.Add( "@sTerm", SqlDbType.Text).Value=searchParam
The problem I'm running into is that the @variable appears to imply single quotes about the value. This is fine for @sTerm, I just removed the quotes from the concatenation and it reads the value correctly, but it appears to break @columnName and the query returns no values. If I manually substitute an appropriate column for @columnName sans single quotes, it works. How can I eliminate the implied single quotes from @columnName so that the query will read it properly?
suppose a table has 4 column,
Name x y z z1
A 1 2 3 3
B 0 2 0 3
C 1 0 3 3
if i pass parameter as @param=1, only column of x should be displayed, if i send parameter as @param=2 only y column should be displayed, if @param=3 is parameter then Z column And Z1 column should be displayed. plz help to create sql query.
The problem is with @NUMERY parameterin code behind i setDim dr As GridViewRowDim numery As Stringnumery = ""
For Each dr In GridView1.RowsDim numeros As Label = dr.Cells(0).Controls(1)numery += numeros.Text & ", "Next
numery = numery.TrimEnd(", ")
SqlDataSource1.UpdateParameters("NUMERY").DefaultValue = numery'so numery will look like this 123, 65465, 54616, 56465Update command looks like this :UpdateCommand="UPDATE slon SET mrowka = @MROWKA WHERE (NUMER IN (@NUMERY))"
<UpdateParameters><asp:Parameter Name="MROWKA" /><asp:Parameter Name="NUMERY" /></UpdateParameters>
And because of @numery i have err: Error converting data type nvarchar to numeric. how should i post "123, 65465, 54616, 56465" as parameter for this query ?
I have a table with month names as columns, below is the structure
CREATE TABLE [MonthlyTarget]
(
[Jan] [int] NULL,
[Feb] [int] NULL,
[March] [int] NULL,
[April] [int] NULL,
[Code] ....
I will store some integer values in these columns,
I want to pass these column names as a parameter,and pull the data from the column.
How to do this..?
In a SPROC I am creating, is there a way to use a columnName as a parameter and then do a filter on that based on a second parameter such as @columnValue ?
So instead of having to construct the WHERE clause or doing a bunch of IF statements to see what the column name is from the parameter and doing a query based upon that, is there a way to tell it to do a WHERE clause where @columnName = @columnValue ?
I do not want to use dynamic SQL string concatenation...
Hello ,please help me
Is it possible to pass a column name through a parameter
something like this
create procedure dbo.paramerers
@parameter varchar
as
select @parameter from table1 where....
because it doesn't give me an error but it also doesn't work
Hi,
Is there a way to pass the column name as a query parameter?? If I use '@Question', like below, I get an error. If I change it to the actual name of the column 'Question1', it works fine. However, I need it to be dynamic so I can pass the column name [Question1, Question2, Question3, etc...] in and not have to define this for each question.
Doesn't Work!!
Code:
SELECT
1.0 * SUM(CASE WHEN @ColumnName > 1 THEN 1 ELSE 0 END) / COUNT(*) AS 'Good',
1.0 * SUM(CASE WHEN @ColumnName = 0 THEN 1 ELSE 0 END)/ COUNT(*) AS 'OK',
1.0 * SUM(CASE WHEN @ColumnName < 0 THEN 1 ELSE 0 END) / COUNT(*) AS 'Poor'
FROM tableA AS A INNER JOIN
tableB AS B ON A.SessionID = B.SessionID
WHERE (A.SurveyID = @SurveyID) AND (A.SubmitDate BETWEEN @BeginDate AND @EndDate)
Works, but I need to pass in the column name dynamically.
Code:
SELECT
1.0 * SUM(CASE WHEN Question1 > 1 THEN 1 ELSE 0 END) / COUNT(*) AS 'Good',
1.0 * SUM(CASE WHEN Question1 = 0 THEN 1 ELSE 0 END)/ COUNT(*) AS 'OK',
1.0 * SUM(CASE WHEN Question1 < 0 THEN 1 ELSE 0 END) / COUNT(*) AS 'Poor'
FROM tableA AS A INNER JOIN
tableB AS B ON A.SessionID = B.SessionID
WHERE (A.SurveyID = @SurveyID) AND (A.SubmitDate BETWEEN @BeginDate AND @EndDate)
Is it possible to assign to a column a value passed as a parameter?
When I run the proc I get the following error :
Msg 245, Level 16, State 1, Procedure Transfer, Line 17
Conversion failed when converting the varchar value '@ID' to data type int.
----####################################################
USE [tbldata]
GO
/****** Object: StoredProcedure [dbo].[Transfer] Script Date: 05/19/2014 11:26:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Transfer](@ID int)
[code].....
can we define a stored procedure parameter which holds a table's column values?
And can we loop in it in a stored procedure?
If yes, could you give a little sql code sample?
Thanx
I am unfortunately lumered with a table I cannot edit that stores a division 2 characters along with boolean fields '0506', '0607', '0708' ... (academic years) to represent if that particular division is active in the current academic year. This has made me have to resort to dynamic sql within a stored procedure to input the appropriate field names.
Is there a better way to do it, it's not mission critical to make it not use dynamic sql but I would prefer not to.
Hi,I am trying to Pass Column Name(FieldName) using Parameter in SQLStatement... But i am getting error...how can i pass Column name using parameter???Example:in table i have fieldname ECountry...Select @FName='ECountry'Select @FName from Table...How it works???Thanx in Advance,Regards,Raghu...
View 5 Replies View RelatedI know passing table/column name as parameter to a stored procedure isnot good practice, but sometimes I need to do that occasionally. Iknow there's a way can do that but forget how. Can someone refresh mymemory?Thanks.Saiyou
View 2 Replies View RelatedHi all,
I'd think this is possible but I'm having trouble getting data returned from the query. Fields PART_NUMBER and INTERNAL_SKU exist in the SKU table. This will be inside a SP. If user passes 'PN' as first parameter then I'd need to have the WHERE clause by PART_NUMBER, if he passes 'SK' (or anything else for now) then the WHERE clause shold be by SKU.
Can't I just build the WHERE by replacing @SearchField with its value ? I've looked up the CASE statement but I don't think it does what I need.
DECLARE @strSearchType varchar(2)
DECLARE @strSearchValue varchar(15)
DECLARE @SearchField varchar(15)
set @strSearchType = 'PN'
set @strSearchValue = '1234567'
IF @strSearchType = 'PN'
begin
set @SearchField = 'PART_NUMBER'
end
ELSE
begin
set @SearchField = 'INTERNAL_SKU'
end
SELECT
SKU as 'SKU',
PART_NUMBER as 'PartNumber',
DESCRIPTION as 'Description'
FROM SKUs
WHERE @SearchField = @strSearchValue
FOR XML PATH('SKU'), ROOT('Response')
Thanks,
ST
Hello,
I tie up columns with parameters.what ever column are selected only those column should be displayed. Now my parameter are string data type.
I write this expression in column-->propertyvisibility--> hide-->Expression
=iif(len(Parameters!VALUEDATE.Value(1)) > "0",false,true)
I am gettin error saying "Index was outside the bounds of the array"
kindly help me !!!
I have a parameter in my report, and if it is set to Null or Zero, I want to hide a column. How would I do this? I have already tried using
Code Snippet=IIF(Parameters!Rate.Value >= 0, false, true)
in the Visibility property for the relevant column, but it doesn't make any difference. The column still shows up even if the Rate parameter is set to Null or zero. I only want the column to show if the user enters a value in the Rate parameter which is greater than zero.
=
i am using asp.net 2005 with sql server 2005. in my database table contains
Table Name : Page_Content
Page_Id
101
102
1
Abc
Pqr
2
Lmn
oiuALTER PROCEDURE [dbo].[SELECT_CONTENT]
(@lang_code varchar(max))
AS
begin declare @a as varchar(max)set @a = @lang_code
Select page_id,@a From page_content
end
Here in this above store procedure i want to pass 101 to @lang_code
here is my output, but this is wrong output
Page_Id
Column1
1
101
2
101
but i want following output
Page_Id
101
102
1
Abc
Pqr
2
Lmn
oiu
I am working on migrating view from Ms Access to SQL server. I got a query and modified it by removing IIF by CASE WHEN. I landed into following query:
Code:
SELECT CASE WHEN <CONDITION>
THEN DATEADD(YYYY,YR1,DATEADD(D,DAY1,TXNDATE))
ELSE 0
END AS CurrentDateAdj,
Year(CurrentDateAdj) + '_' + 'some text and processing')
FROM INCREMENTDATATABLE;
Here DAY1 and YR1 are from INCREMENTDATATABLE.
I am getting error that CurrentDateAdj not found. How can I fix this?
I trying to create a general stored procedure which updates 1 out of 140 columns depending on the column name provided as a parameter.
I'm not having much luck, just wondering if anyone else had tried to do this and whether it is actually possible?
Any help would be much appreciated
Chris
I have a split string function that will take a comma delimited string and give back a table with all the values.I have a table that has a column with a comma delimited comma delimited list of states.
I can use a where clause to find one state in the table (such as all records that have CA in the states string).But need to find out how to find all the rows that have all or any of the states from a comma delimited parameter.Here is the schema
CREATE FUNCTION [dbo].[split] (@list nvarchar(MAX))
RETURNS @tbl TABLE (svar nvarchar(10) NOT NULL) AS
BEGIN
DECLARE @pos int,
@nextpos int,
@valuelen int
[code]....
Hi!I want to pass a column name, sometimes a table name, to a storedprocedure, but it didn't work. I tried to define the data type aschar, vachar, nchar, text, but the result were same. Any one know howto get it work?Thanks a lot!!Saiyou
View 1 Replies View RelatedAll I did was change the names on two measures in a measure group, tried to refresh the DSV and ka-blooey!
'column' argument cannot be null parameter name:column
Hello all.
I am on the verge of being able to do exactly what I want, but just can't seem to find the right combination of things to do it. I'm sure all of you wonderful folks will be able to point it out to me immediately, but I've been looking at it too long or something....
I have a record of individual sales with the state, and quarter of the sale.
sale_id state quarter
001 NY 2005Q1
003 WI 2006Q2
etc.
I create a report with a matrix to show count(sale_id) with Quarter as the column group and State as the row group. This works fine.
Now what I want to do is to get percentages based on quarterly sales. In other words, what percent of sales for 2005Q1 in NY vs. all sales in 2005Q1. So I create a second dataset (called total) with an SQL query like so:
SELECT count(sale_id)
FROM data_table
WHERE quarter = @QueryQuarter
Now, back in the matrix I want to use the column that we're in (2005Q1, 2005Q2, etc.) as the value that is passed to this query.
This is a simple concept, but I can't seem to figure out the correct call to pass the column group to the query as the parameter.
Thank you for any pointers you might be able to give. As I said, I'm right on the verge and just can't quite get it.
cmk
How can do this. Because my stored function contains same clause except colums name.So I want to use column name as a parameter but how can send column name and use it like Sum(parameter) function .
my procedure like this:
ALTER PROCEDURE [dbo].[ORNEK10]
@YIL VARCHAR(4),
@TEKLIF_TURU VARCHAR(255)
AS
BEGIN
DECLARE @N1 FLOAT
DECLARE @N2 FLOAT
SET @N1 = ( SELECT DEGERI FROM PARAMETRE WHERE PARAMETRE_ADI='N1')
SET @N2 = ( SELECT DEGERI FROM PARAMETRE WHERE PARAMETRE_ADI='N2')
SET NOCOUNT ON;
--I want to avoid using if statements for Sum() function
IF(@TEKLIF_TURU='BASKAN_TEKLIF')
SELECT TOP (100) PERCENT KOD1, KOD2, KOD3, KOD4, dbo.ORNEK10AD(KOD1, KOD2, KOD3, KOD4) AS ACIKLAMA,
SUM(BASKAN_TEKLIF) AS YILI,
((100+@N1)*SUM(BASKAN_TEKLIF))/100 AS YIL1,
((100+@N1)*(100+@N2)*SUM(BASKAN_TEKLIF))/10000 AS YIL2
FROM GELIR AS G WHERE YIL = @YIL GROUP BY KOD1, KOD2, KOD3, KOD4 WITH ROLLUP
ORDER BY KOD1, KOD2, KOD3, KOD4
IF(@TEKLIF_TURU='ENCUMEN_TEKLIF')
SELECT TOP (100) PERCENT KOD1, KOD2, KOD3, KOD4, dbo.ORNEK10AD(KOD1, KOD2, KOD3, KOD4) AS ACIKLAMA,
SUM(ENCUMEN_TEKLIF) AS YILI,
((100+@N1)*SUM(ENCUMEN_TEKLIF))/100 AS YIL1,
((100+@N1)*(100+@N2)*SUM(ENCUMEN_TEKLIF))/10000 AS YIL2
FROM GELIR AS G WHERE YIL = @YIL GROUP BY KOD1, KOD2, KOD3, KOD4 WITH ROLLUP
ORDER BY KOD1, KOD2, KOD3, KOD4
IF(@TEKLIF_TURU='MECLIS_TEKLIF')
SELECT TOP (100) PERCENT KOD1, KOD2, KOD3, KOD4, dbo.ORNEK10AD(KOD1, KOD2, KOD3, KOD4) AS ACIKLAMA,
SUM(MECLIS_TEKLIF) AS YILI,
((100+@N1)*SUM(MECLIS_TEKLIF))/100 AS YIL1,
((100+@N1)*(100+@N2)*SUM(MECLIS_TEKLIF))/10000 AS YIL2
FROM GELIR AS G WHERE YIL = @YIL GROUP BY KOD1, KOD2, KOD3, KOD4 WITH ROLLUP
ORDER BY KOD1, KOD2, KOD3, KOD4
END
How can a Stored Procedure use a variable table name and column name ?
The statement :-
SELECT @columnname FROM @tablename
gives error "Line 5: Incorrect syntax near '@tablename'."
I am passing the parameters 'tablename' and 'columnname' into the stored procedure, (in order to select a variable column from a variable table).
If I hard-code the tablename, then I get a column of output with just the name of the column I was trying to list, e.g. surname,
i.e. SELECT @columnname FROM employeetable gives :-
surname
surname
surname
surname
surname
surname
How can I get the procedure to accept a variable table name and column name ?
I thought one way to do it would be to use the 'EXEC' statement :-
SELECT @sql = 'SELECT '+ @columnname +' FROM '+@tablename
EXEC(@sql)
but this gives error : "Incorrect syntax near the keyword 'FROM' "
although the following table works :-
SELECT @sql = 'SELECT surname FROM '+@tablename
EXEC(@sql)
The problem seems to be mainly with variable column names.
Also, we don't really want to use the EXEC statement because it will not be compiled and will be slower. (Does anyone know by how much slower ?)
Any advice would be appreciated.
Kind regards,
Ian.
(ian.mitchell@sds.no)
I have a temp table that hosts data from a MDX query, I want to select a column from that table by passing in a parameter and then insert to a new table.
Here's the table structure, let's call it #temp
[Product].[Product Hierarchy].[level 03].[Member_CAPTION], [Measures].[Gross Sales]
Bike, 200
Accessory , 100
I have a table in the DB created as [ProductSales]
Here's what works for me:
insert into [ProductSales](Product, Sales)
Select convert(varchar(20), "[Product].[Product Hierarchy].[level 03].[Member_CAPTION]") as 'Product' , convert(decimal(18,2), [Measures].[Data]) as 'Sales'
From #temp
But in reality the product level is not always on level 03, I want to define a parameter @Product and pass it to the select list.
Here's what I tried to do:
Declare @Product varchar(500) = convert(varchar(20), "[Product].[Product Hierarchy].[level 01].[Member_CAPTION]")
Declare @SQL varchar(MAX)
SET @SQL='insert into [ProductSales](Product, Sales)
Select '+@Product+' as 'Product' , convert(decimal(18,2), [Measures].[Data]) as 'Sales'
From #temp'
Exec @SQL
But it threw me error, what is the right way to do it, is it because the double quote or single quote thing?
I have a report with a date type parameter. Depending on the value return by this date type parameter the dataset will return either the credit, deposit or process date. How do I go about coding it so that it will dynamically select the right column in my query for my dataset?
Sincerely appreciate all the help I can get.
Thanks in advance.
Hello Everyone,A have a Managed Stored Procedure ([Microsoft.SqlServer.SqlProcedure]). In it I would like to call a UserDefinedFunction:public static SqlInt32 IsGetSqlInt32Null(SqlDataReader dr, Int32 index) { if(dr.GetSqlValue(index) == null) return SqlInt32.Null; else return dr.GetSqlInt32(index) }I than allways get the following ErrorMessage:Column, parameter, or variable #1: Cannot find data type SqlDatareader.Is it not possibel to pass the SqlDatareader to a SqlFunction, do the reading there and return the result.My original Problem is, that datareader.GetSqlInt32(3) throws an error in case there is Null in the DB. I thought SqlInt32 would allow Null.Would appreciate any kind of help! Thanks
View 1 Replies View RelatedHi, Could you tell me if this is possible? How do I return results using an entire column as part of the search parameter? I need to do this in the sql rather than selecting the contents and iterating through it as it would take too long.
eg.
CREATE TABLE [dbo].[tPopupKeywords](
[id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[title] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[description] [nvarchar](2000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
INSERT INTO dbo.tPopupKeywords(title, description)
SELECT 'check', 'desc' UNION ALL
SELECT 'for', 'desc' UNION ALL
SELECT 'keywords', 'desc'
select dbo.tpopupkeywords.title
where 'This is an example of a passed in string to check if any keywords are returned.'
LIKE '% ' + dbo.tpopupkeywords.title + ' %' --Does this bit need to do a select??
expected results.....:
check
keywords
In Oracle when i create any procedure i define parameter datatype linked to under lying table.
For ex
create procedure testprocedure
(param1 customer.name%type,
param2 customer.salary%type )
Here i have defined param1 and param2 with datatype of name and salary of customer table respectively.
This way i do not need to worry about modifying param1 and param2 datatypes when datatypes of name & salary of customer table changes in future.
How can i accomplish this in SQL server.
i have below table in DB
DB Table
ID
Column
Row data
1
Supplier CODE
1001
2
Supplier Name
ACB
3
Product
7K7
4
Price
1000
now I create one report parameter order1 IF I will give order1.value=1,2,3 then Report will come like this :--
Suppliercode
Supplier Name
Product
1001
ACB
7K7
IF I will give order1.value=3,2,1 then Report will come like this :--
Product
Supplier Name
Suppliercode
7K7
ACB
1001
IF I will give order1.value=1,3 then Report will come like this :--
Suppliercode
Product
1001
7K7
Hi,Basically I have a Matrix report with two column drilldown parameters, named "result" and "detail of the result". Then When I click on the detail cell I am passing two parameters to filter the linked report.The first one is quite direct I pass it and it's allright.The second one has me crazy... If I pass it while the second column is collapsed, the first value is passed.... When I am not choosing anyone and the sum of all the columns is shown, I need (and surely all of us) That when a drilldown column is collapsed, the respective value of the column that I pass to the parameter is passed as null or better, That I am able to detect when a column is collapsed or not, for just not passing the parameter (faster).Well any help or guidance would help as I am lost on this.Thanks for the help,Jose
The only workaround (partially) I have found is, on the TextBox properties, Navigation tab, Parameters details is to assign the following expression to the Omit column:
=IIf(IsNothing(Fields!Det_Result.Value) Or (Trim(Fields!Det_Resul.Value)="") , True, False)
But that only works when the parameter is text and is a null or empty value... not nice...
is there any way or a tool to identify if in procedure the Parameter length was declarated less than table Column length ..
I have a table
CREATE TABLE TEST001 (KeyName Varchar(100) ) a procedure
CREATE PROCEDURE SpFindNames ( @KeyName VARCHAR(40) )
AS
BEGIN
SELECT KeyName FROM TEST001
WHERE KeyName = @KeyName
END
KeyName = @KeyName
Here table Column with 100 char length "KeyName" was compared with SP parameter "@KeyName" with length 40 char ..
IS there any way to find out all such usage on the ALL Procedures in the Database ?